public void BookGuest(string firstName, string lastName, DateTime startDate, DateTime endDate, int roomTypeId)
{
string sql = @"select 1 from Guests where FirstName = @firstName and LastName = @lastName";
int results = _db.LoadData <dynamic, dynamic>(sql, new { firstName, lastName }, connectionStringName).Count();
if (results == 0)
{
sql = @"insert into Guests (FirstName, LastName)
values (@firstName, @lastName);" ;
_db.SaveData(sql, new { firstName, lastName }, connectionStringName);
}
sql = @"select [Id], [FirstName], [LastName]
from Guests
where FirstName = @firstName and LastName = @lastName LIMIT 1;" ;
GuestModel guest = _db.LoadData <GuestModel, dynamic>(sql,
new { firstName, lastName },
connectionStringName).First();
RoomTypeModel roomType = _db.LoadData <RoomTypeModel, dynamic>("select * from RoomTypes where Id = @Id",
new { Id = roomTypeId },
connectionStringName).First();
TimeSpan timeStaying = endDate.Date.Subtract(startDate.Date);
sql = @"select r.*
from Rooms r
inner join RoomTypes t on t.Id = r.RoomTypeId
where r.RoomTypeId = @roomTypeId
and r.Id not in (
select b.RoomId
from Bookings b
where (@startDate < b.StartDate and @endDate > b.EndDate)
or (b.StartDate <= @endDate and @endDate < b.EndDate)
or (b.StartDate <= @startDate and @startDate < b.EndDate)
);" ;
List <RoomModel> availableRooms = _db.LoadData <RoomModel, dynamic>(sql,
new { startDate, endDate, roomTypeId },
connectionStringName);
sql = @"insert into Bookings(RoomId, GuestId, StartDate, EndDate, TotalCost)
values (@roomId, @guestId, @startDate, @endDate, @totalCost);" ;
_db.SaveData(sql,
new
{
roomId = availableRooms.First().Id,
guestId = guest.Id,
startDate = startDate,
endDate = endDate,
totalCost = timeStaying.Days * roomType.Price
},
connectionStringName);
}
public void BookGuest(string firstName, string lastName, int roomTypeId, DateTime startDate, DateTime endDate)
{
string sqlStatement = "INSERT INTO Guests (FirstName, LastName) " +
"SELECT @firstName as FirstName, @lastName as LastName " +
"WHERE not exists (" +
"SELECT 1 " +
"FROM Guests " +
"WHERE FirstName = @firstName and LastName = @lastName); " +
"SELECT Id, FirstName, LastName " +
"FROM Guests " +
"WHERE FirstName = @firstName and LastName = @lastName";
Guest guest = _db.LoadData <Guest, dynamic>(sqlStatement,
new { firstName, lastName },
connectionStringName).First();
// Get room type for later calculating total cost
RoomType roomType = _db.LoadData <RoomType, dynamic>("select * from RoomTypes where Id = @Id",
new { Id = roomTypeId },
connectionStringName).First();
sqlStatement = "SELECT rt.Id, rt.Title, rt.Description, rt.Price " +
"FROM RoomTypes rt " +
"INNER JOIN Rooms r ON rt.Id = r.RoomTypeId " +
"WHERE r.RoomTypeId = @Id " +
"AND " +
"r.Id not in (" +
"SELECT b.RoomId " +
"FROM Bookings b " +
"WHERE (@startDate <= b.EndDate and @endDate >= b.StartDate)) " +
"GROUP BY rt.Id, rt.Title, rt.Description, rt.Price;";;
// get available rooms
List <Room> availableRooms = _db.LoadData <Room, dynamic>(sqlStatement,
new { startDate, endDate, Id = roomTypeId },
connectionStringName);
TimeSpan timeStaying = endDate.Date.Subtract(startDate.Date);
sqlStatement = "INSERT INTO Bookings (RoomId, GuestId, StartDate, EndDate, CheckedIn, TotalCost) " +
"VALUES (@roomId, @guestId, @startDate, @endDate, @checkedIn, @totalCost);";
// Book room
_db.SaveData(sqlStatement,
new
{
roomId = availableRooms.First().Id,
guestId = guest.Id,
startDate = startDate,
endDate = endDate,
checkedIn = false,
totalCost = timeStaying.Days * roomType.Price
},
connectionStringName);
}
public List <PersonModel> LoadPeople()
{
string sql = "select * from Person";
var output = _database.SaveData <PersonModel>(sql);
// I commented it out since it was meant to show how to fail the test
//foreach (var item in output)
//{
// if (item.FirstName == "Tim")
// {
// item.FirstName = "Timothy";
// }
//}
return(output);
}
public void SavePerson(PersonModel person)
{
string sql = "insert into Person (FirstName, LastName, HeightInInches) " +
"values (@FirstName, @LastName, @HeightInInches)";
_database.SaveData(person, sql);
}
public void SavePerson(PersonModel person)
{
string sql = "insert into Person (FirstName, LastName, HeightInInches) " +
"values (@FirstName, @LastName, @HeightInInches)";
sql = sql.Replace("@FirstName", $"'{person.FirstName}'");
sql = sql.Replace("@LastName", $"'{person.LastName}'");
sql = sql.Replace("@HeightInInches", $"{person.HeightInInches}");
_database.SaveData(person, sql);
}
public void SavePerson(PersonModel person)
{
string sql = "insert into Person (FirstName, LastName, HeightInInches) " +
"values (@FirstName, @LastName, @HeightInInches)";
/// If this for loop below is active then the 'Times.Exactly(1)' in the PersonProcessorTests would fail.
//for (int i = 0; i < 10; i++)
//{
// _database.SaveData(person, sql);
//}
sql = sql.Replace("@FirstName", $"'{person.FirstName}'");
sql = sql.Replace("@LastName", $"'{person.LastName}'");
sql = sql.Replace("@HeightInInches", $"{person.HeightInInches}");
_database.SaveData(person, sql);
}