public List <PersonModel> LoadPeople()
{
string sql = "select * from Person";
var output = _database.LoadData <PersonModel>(sql);
return(output);
}
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 List <CrudModel> LoadData()
{
try
{
var output = _database.LoadData <CrudModel>(List);
_log.Info(new LogDetails().SetLogClass(this.GetType().Name).SetLogMethod(LogDetails.GetCurrentMethod()));
return(output);
}
catch (Exception ex)
{
_log.Error(ex);
return(new List <CrudModel>());
}
}
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.LoadData <PersonModel>(sql);
//foreach (var item in output)
//{
// if (item.FirstName == "Camilo")
// {
// item.FirstName = "Jaiver Camilo";
// }
//}
return(output);
}
public List <PersonModel> LoadPeople()
{
string sql = "select * from Person";
var output = _database.LoadData <PersonModel>(sql);
// Introduces a failure just for the sake of tests...
//foreach (var person in output)
//{
// if (person.FirstName == "Zoli")
// {
// person.FirstName = "Zoltan";
// }
//}
return(output);
}
public List <IPersonModel> LoadPeople()
{
List <IPersonModel> output = new List <IPersonModel>();
string sql = "select * from Person";
List <PersonModel> loadDataResults = _database.LoadData <PersonModel>(sql);
foreach (IPersonModel item in loadDataResults)
{
output.Add(item);
}
//foreach (var item in output)
//{
// if (item.FirstName == "Tim")
// {
// item.FirstName = "Timothy";
// }
//}
return(output);
}
public List <PersonModel> LoadPeople()
{
string sql = "select * from Person";
return(_database.LoadData <PersonModel>(sql));
}