static void getAllReservationsJoin()
{
WriteLine("List of SmallHotelDB reservations: ");
using (var db = new SmallHotelDB())
{
var users = db.Users.Select(u => new { u.UserID, u.UserName }).ToArray();
var rooms = db.Rooms.Select(r => new { r.RoomID, r.RoomName }).ToArray();
var reservations = db.Reservations.Select(r => new { r.RoomID, r.UserID, r.ReservationStartDate, r.ReservationEndDate });
var queryUserReservation = users.Join(reservations,
user => user.UserID,
reservation => reservation.UserID,
(u, r) => new { u.UserName, r.RoomID, r.ReservationStartDate, r.ReservationEndDate });
var queryUserReservationRoom = rooms.Join(queryUserReservation,
room => room.RoomID,
qur => qur.RoomID,
(r, ur) => new { r.RoomName, ur.UserName, ur.ReservationStartDate, ur.ReservationEndDate }
);
foreach (var item in queryUserReservationRoom)
{
WriteLine($"User \"{item.UserName}\" is in room \"{item.RoomName}\" from {item.ReservationStartDate} to {item.ReservationEndDate}");
}
}
}
public string Get(string userName, int roomID, int startYear, int startMonth, int startDay, int endYear, int endMonth, int endDay)
{
using (var db = new SmallHotelDB())
{
//id if found, otherwise -1
int userId = -1;
IQueryable <User> users = db.Users.Where(u => EF.Functions.Like(u.UserName, userName));
if (users.Count() == 0)
{
db.Users.Add(new User {
UserName = userName
});
db.SaveChanges();
users = db.Users.Where(u => EF.Functions.Like(u.UserName, userName));
}
User user = users.ToArray <User>()[0];
userId = user.UserID;
DateTime startDateTime = new DateTime(startYear, startMonth, startDay, 14, 0, 0);
DateTime endDateTime = new DateTime(endYear, endMonth, endDay, 10, 0, 0);
Reservation reservation = new Reservation {
UserID = userId, RoomID = roomID, ReservationStartDate = startDateTime, ReservationEndDate = endDateTime
};
db.Reservations.Add(reservation);
int affected = db.SaveChanges();
String response = "Total " + affected.ToString() + " reservation made";
return(Newtonsoft.Json.JsonConvert.SerializeObject(response));
}
}
public string Get(int id)
{
using (var db = new SmallHotelDB())
{
IQueryable <Room> rooms = db.Rooms.Where(r => r.RoomID == id);
foreach (Room r in rooms)
{
return(Newtonsoft.Json.JsonConvert.SerializeObject(r));
}
return("No room!");
}
}
static void getAllReservations()
{
WriteLine("List of SmallHotelDB reservations: ");
using (var db = new SmallHotelDB())
{
IQueryable <Reservation> reservations = db.Reservations;
foreach (Reservation r in reservations)
{
WriteLine($"{r.ReservationID} in room {r.RoomID} was reserved from {r.ReservationStartDate} to {r.ReservationEndDate} by {r.UserID}");
}
}
}
static void getAllRooms()
{
WriteLine("List of SmallHotelDB rooms: ");
using (var db = new SmallHotelDB())
{
IQueryable <Room> rooms = db.Rooms;
foreach (Room r in rooms)
{
WriteLine($"{r.RoomID} has name {r.RoomName} for {r.RoomCapacity} people with wifi {r.RoomWiFi} and minibar {r.RoomMinibar}");
}
}
}
public string Get(int id)
{
using (var db = new SmallHotelDB())
{
IQueryable <User> users = db.Users.Where(u => u.UserID == id);
foreach (User u in users)
{
return(Newtonsoft.Json.JsonConvert.SerializeObject(u));
}
return("No user!");
}
}
static void getAllUsers()
{
WriteLine("List of SmallHotelDB users: ");
using (var db = new SmallHotelDB())
{
IQueryable <User> users = db.Users
.Include(u => u.Reservations);
foreach (User u in users)
{
WriteLine($" {u.UserID} : {u.UserName} has {u.Reservations.Count()} reservations.");
}
}
}
public string Get()
{
List <User> listOfUsers = new List <User>();
using (var db = new SmallHotelDB())
{
IQueryable <User> users = db.Users;
string item = string.Empty;
foreach (User u in users)
{
listOfUsers.Add(u);
}
}
return(Newtonsoft.Json.JsonConvert.SerializeObject(listOfUsers));
}
private static void getFreeRoomsForAPeriod(int startYear, int startMonth, int startDay, int endYear, int endMonth, int endDay)
{
WriteLine("List of free rooms: ");
DateTime startDateTime = new DateTime(startYear, startMonth, startDay, 14, 0, 0);
DateTime endDateTime = new DateTime(endYear, endMonth, endDay, 10, 0, 0);
using (var db = new SmallHotelDB())
{
var reservations = db.Reservations.Select(r => new { r.RoomID, r.UserID, r.ReservationStartDate, r.ReservationEndDate });
var occupiedRooms = db.Reservations
.Where(r => r.ReservationStartDate < endDateTime && startDateTime < r.ReservationEndDate)
.Select(r => new { r.RoomID }).Distinct().ToList();
var allRooms = db.Rooms.Select(r => new { r.RoomID }).Distinct().ToList();
var freeRooms = allRooms.Except(occupiedRooms).Distinct().ToList();
var rooms = db.Rooms.Select(r => new { r.RoomID, r.RoomName }).ToArray();
var completeFreeRooms = db.Rooms.Join(freeRooms,
completeFreeRoom => completeFreeRoom.RoomID,
room => room.RoomID,
(c, r) => new { c.RoomID, c.RoomName }
);
String resultJson = Newtonsoft.Json.JsonConvert.SerializeObject(completeFreeRooms);
//test reports
foreach (var item in occupiedRooms)
{
WriteLine($"Room \"{item.RoomID}\" is occupied ");
}
foreach (var item in allRooms)
{
WriteLine($"Room \"{item.RoomID}\" exists ");
}
foreach (var item in freeRooms)
{
WriteLine($"Room \"{item.RoomID}\" is free ");
}
foreach (var item in completeFreeRooms)
{
WriteLine($"Free room ID\"{item.RoomID}\" has name {item.RoomName}");
}
WriteLine(resultJson);
}
}
public string Get()
{
StringBuilder sb = new StringBuilder();
List <Room> listOfRooms = new List <Room>();
using (var db = new SmallHotelDB())
{
IQueryable <Room> rooms = db.Rooms;
string item = string.Empty;
foreach (Room r in rooms)
{
listOfRooms.Add(r);
}
}
return(Newtonsoft.Json.JsonConvert.SerializeObject(listOfRooms));
}
public string Get()
{
List <ReservationExtended> listOfReservationExtended = new List <ReservationExtended>();
using (var db = new SmallHotelDB())
{
var users = db.Users.Select(u => new { u.UserID, u.UserName }).ToArray();
var rooms = db.Rooms.Select(r => new { r.RoomID, r.RoomName }).ToArray();
var reservations = db.Reservations.Select(r => new { r.ReservationID, r.RoomID, r.UserID, r.ReservationStartDate, r.ReservationEndDate });
var queryUserReservation = users.Join(reservations,
user => user.UserID,
reservation => reservation.UserID,
(u, r) => new { r.ReservationID, u.UserID, u.UserName, r.RoomID, r.ReservationStartDate, r.ReservationEndDate });
var queryUserReservationRoom = rooms.Join(queryUserReservation,
room => room.RoomID,
qur => qur.RoomID,
(r, ur) => new { ur.ReservationID, r.RoomID, ur.UserID, r.RoomName, ur.UserName, ur.ReservationStartDate, ur.ReservationEndDate }
);
foreach (var item in queryUserReservationRoom)
{
ReservationExtended re = new ReservationExtended
{
ReservationID = item.ReservationID,
RoomID = item.RoomID,
UserID = item.UserID,
ReservationStartDate = item.ReservationStartDate,
ReservationEndDate = item.ReservationEndDate,
RoomName = item.RoomName,
UserName = item.UserName
};
listOfReservationExtended.Add(re);
}
return(Newtonsoft.Json.JsonConvert.SerializeObject(listOfReservationExtended));
}
}
public string Get(int startYear, int startMonth, int startDay, int endYear, int endMonth, int endDay)
{
DateTime startDateTime = new DateTime(startYear, startMonth, startDay, 14, 0, 0);
DateTime endDateTime = new DateTime(endYear, endMonth, endDay, 10, 0, 0);
String result = String.Empty;
using (var db = new SmallHotelDB())
{
var reservations = db.Reservations.Select(r => new { r.RoomID, r.UserID, r.ReservationStartDate, r.ReservationEndDate });
var occupiedRooms = db.Reservations
.Where(r => r.ReservationStartDate < endDateTime && startDateTime < r.ReservationEndDate)
.Select(r => new { r.RoomID }).Distinct().ToList();
var allRooms = db.Rooms.Select(r => new { r.RoomID }).Distinct().ToList();
var freeRooms = allRooms.Except(occupiedRooms).Distinct().ToList();
var rooms = db.Rooms.Select(r => new { r.RoomID, r.RoomName }).ToArray();
var completeFreeRooms = db.Rooms.Join(freeRooms,
completeFreeRoom => completeFreeRoom.RoomID,
room => room.RoomID,
(c, r) => new { c.RoomID, c.RoomName }
);
result = Newtonsoft.Json.JsonConvert.SerializeObject(completeFreeRooms);
}
return(result);
}
