/// <summary>
/// the method to retrieve data form the database about the bookings for a Escape-room and on that date
/// </summary>
/// <param name="EscID"> the escape-room id </param>
/// <param name="Bdate"> the date of the chosen day to the booking </param>
/// <returns> returns a list whit all bookings for the escape-room on that date</returns>
public List <Booking> CheckBooking(int EscID, DateTime Bdate)
{
Booking TempBook;
List <Booking> book = new List <Booking>();
DBEscapeRoom dber = new DBEscapeRoom();
DBEmployee dbemp = new DBEmployee();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand cmdGetBook = connection.CreateCommand())
{
cmdGetBook.CommandText = "SELECT Booking.* FROM Booking WHERE EscapeRoomID =@EscapeRoomID AND BDate =@BDate";
cmdGetBook.Parameters.AddWithValue("@EscapeRoomID", EscID);
cmdGetBook.Parameters.AddWithValue("@BDate", Bdate);
SqlDataReader reader = cmdGetBook.ExecuteReader();
while (reader.Read())
{
TempBook = new Booking();
TempBook.amountOfPeople = reader.GetInt32(reader.GetOrdinal("AmountOfPeople"));
TempBook.bookingTime = reader.GetTimeSpan(reader.GetOrdinal("BookingTime"));
TempBook.date = reader.GetDateTime(reader.GetOrdinal("BDate"));
TempBook.emp = dbemp.Get(reader.GetInt32(reader.GetOrdinal("EmployeeID")));
TempBook.er = dber.GetForOwner(EscID);
book.Add(TempBook);
}
}
}
return(book);
}
/// <summary>
/// the method to get all booking on a escape-room for the database
/// </summary>
/// <param name="EscId">escape-room id</param>
/// <returns> a list off booking on that escape-room </returns>
public IEnumerable <Booking> GetAllOneRoom(int EscId)
{
List <Booking> books = new List <Booking>();
Booking tempBook;
DBCustomer dbcus = new DBCustomer();
DBEscapeRoom dber = new DBEscapeRoom();
DBEmployee dbemp = new DBEmployee();
using (SqlConnection connection = new SqlConnection(_connectionString)) {
connection.Open();
using (SqlCommand cmdGetBook = connection.CreateCommand()) {
cmdGetBook.CommandText = "SELECT Booking.* FROM Booking WHERE EscapeRoomID = @EscapeRoomID";
cmdGetBook.Parameters.AddWithValue("@EscapeRoomID", EscId);
SqlDataReader reader = cmdGetBook.ExecuteReader();
while (reader.Read())
{
tempBook = new Booking()
{
Id = reader.GetInt32(reader.GetOrdinal("BookingID")),
amountOfPeople = reader.GetInt32(reader.GetOrdinal("AmountOfPeople")),
bookingTime = reader.GetTimeSpan(reader.GetOrdinal("BookingTime")),
cus = dbcus.Get(reader.GetString(reader.GetOrdinal("UserName"))),
date = reader.GetDateTime(reader.GetOrdinal("BDate")),
emp = dbemp.Get(reader.GetInt32(reader.GetOrdinal("EmployeeID"))),
er = dber.GetForOwner(reader.GetInt32(reader.GetOrdinal("EscapeRoomID")))
};
books.Add(tempBook);
}
}
}
return(books);
}
/// <summary>
/// Getting a Booking for a user on the date and escape-room id in the database
/// </summary>
/// <param name="EscID">escape-room id</param>
/// <param name="username">users user-name </param>
/// <param name="Bdate">the date there has bin chosen</param>
/// <returns> the complete info about the booking there has bin asked for </returns>
public Booking Get(int EscID, string username, DateTime Bdate)
{
Booking book = new Booking();
DBCustomer dbcus = new DBCustomer();
DBEscapeRoom dber = new DBEscapeRoom();
DBEmployee dbemp = new DBEmployee();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (SqlCommand cmdGetBook = connection.CreateCommand())
{
cmdGetBook.CommandText = "SELECT Booking.* FROM Booking WHERE UserName =@UserName AND EscapeRoomID =@EscapeRoomID AND BDate =@BDate";
cmdGetBook.Parameters.AddWithValue("@UserName", username);
cmdGetBook.Parameters.AddWithValue("@EscapeRoomID", EscID);
cmdGetBook.Parameters.AddWithValue("@BDate", Bdate);
SqlDataReader reader = cmdGetBook.ExecuteReader();
if (reader.Read())
{
book.amountOfPeople = reader.GetInt32(reader.GetOrdinal("AmountOfPeople"));
book.bookingTime = reader.GetTimeSpan(reader.GetOrdinal("BookingTime"));
book.date = reader.GetDateTime(reader.GetOrdinal("BDate"));
book.cus = dbcus.Get(reader.GetString(reader.GetOrdinal("UserName")));
book.emp = dbemp.Get(reader.GetInt32(reader.GetOrdinal("EmployeeID")));
book.er = dber.GetForOwner(reader.GetInt32(reader.GetOrdinal("EscapeRoomID")));
book.Id = reader.GetInt32(reader.GetOrdinal("BookingID"));
}
}
}
return(book);
}
/// <summary>
/// the method to create a new escape-room whit all the info about it and image to the database
/// </summary>
/// <param name="name"> the title off the room</param>
/// <param name="description">the telling about the room</param>
/// <param name="maxClearTime"> how long the customer can use in the room before he have to quit </param>
/// <param name="cleanTime"> how long it will tack to clean the room and a new customer can use it</param>
/// <param name="price"> the price for the room</param>
/// <param name="rating"> a number to set to be 0 from start</param>
/// <param name="empId"> employee id</param>
/// <param name="img"> a image in bytes</param>
public void Create(string name, string description, decimal maxClearTime, decimal cleanTime, decimal price, decimal rating, int empId, byte[] img)
{
EscapeRoom escapeRoom = new EscapeRoom();
String tempCheck;
DBEmployee DBemp = new DBEmployee();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
using (IDbTransaction tran = connection.BeginTransaction())
try
{
using (SqlCommand cmdReadERs = connection.CreateCommand())
{
cmdReadERs.CommandText = "INSERT INTO EscapeRoom(EsName, EsDescription, MaxClearTime, CleanTime, Price, Rating, EmployeeID, Image)" +
"VALUES(@EsName, @EsDescription, @MaxClearTime, @CleanTime, @Price, @Rating, @EmployeeID, @Image)";
cmdReadERs.Transaction = tran as SqlTransaction;
cmdReadERs.Parameters.AddWithValue("EsName", name);
cmdReadERs.Parameters.AddWithValue("EsDescription", description);
cmdReadERs.Parameters.AddWithValue("MaxClearTime", maxClearTime);
cmdReadERs.Parameters.AddWithValue("CleanTime", cleanTime);
cmdReadERs.Parameters.AddWithValue("Price", price);
cmdReadERs.Parameters.AddWithValue("Rating", rating);
cmdReadERs.Parameters.AddWithValue("EmployeeId", empId);
if (img != null && img.Length > 0)
{
cmdReadERs.Parameters.AddWithValue("Image", img);
}
else
{
cmdReadERs.Parameters.Add("Image", System.Data.SqlDbType.VarBinary, -1);
cmdReadERs.Parameters["Image"].Value = DBNull.Value;
}
cmdReadERs.ExecuteNonQuery();
tran.Commit();
}
}
catch (Exception e)
{
tran.Rollback();
Console.WriteLine(e);
Console.ReadLine();
}
}
}
/// <summary>
/// the method to get all escape-rooms for the database whit all the info about the rooms
/// </summary>
/// <returns>a list off escape-rooms</returns>
public IEnumerable <EscapeRoom> GetAllForOwner()
{
List <EscapeRoom> EscapeRooms = new List <EscapeRoom>();
EscapeRoom tempER;
String tempCheck;
DBEmployee EmpDB = new DBEmployee();
;
using (SqlConnection connection = new SqlConnection(_connectionString)) {
connection.Open();
using (SqlCommand cmdReadAllEs = connection.CreateCommand()) {
cmdReadAllEs.CommandText = "SELECT EscapeRoom.*, CheckList.CheckList FROM EscapeRoom LEFT JOIN CheckList ON EscapeRoom.EscapeRoomID = CheckList.EscapeRoomID ";
SqlDataReader reader = cmdReadAllEs.ExecuteReader();
while (reader.Read())
{
tempER = new EscapeRoom();
tempER.escapeRoomID = reader.GetInt32(reader.GetOrdinal("EscapeRoomID"));
tempER.name = reader.GetString(reader.GetOrdinal("EsName"));
tempER.price = reader.GetDecimal(reader.GetOrdinal("Price"));
tempER.maxClearTime = reader.GetInt32(reader.GetOrdinal("MaxClearTime"));
tempER.cleanTime = reader.GetInt32(reader.GetOrdinal("CleanTime"));
tempER.description = reader.GetString(reader.GetOrdinal("EsDescription"));
tempER.rating = reader.GetDecimal(reader.GetOrdinal("Rating"));
tempER.emp = EmpDB.Get(reader.GetInt32(reader.GetOrdinal("EmployeeID")));
tempER.Image = (byte[])reader.GetSqlBinary(reader.GetOrdinal("Image"));
int i = 0;
//while (reader.GetString(reader.GetOrdinal("CheckList")).Length > i) {
// tempCheck = reader.GetString(reader.GetOrdinal("CheckList"));
// tempER.AddToList(tempCheck);
// i++;
//}
EscapeRooms.Add(tempER);
}
}
}
return(EscapeRooms);
}
/// <summary>
/// the method to get a escape-room from the database
/// </summary>
/// <param name="ER_ID">escape-rooms id</param>
/// <returns>a escape-room whit all the info about it</returns>
public EscapeRoom GetForOwner(int ER_ID)
{
EscapeRoom escapeRoom = new EscapeRoom();
String tempCheck;
DBEmployee DBemp = new DBEmployee();
using (SqlConnection connection = new SqlConnection(_connectionString)) {
connection.Open();
using (SqlCommand cmdReadERs = connection.CreateCommand()) {
cmdReadERs.CommandText = "SELECT EscapeRoom.*, CheckList.CheckList From EscapeRoom LEFT JOIN CheckList ON " +
"EscapeRoom.EscapeRoomID = CheckList.EscapeRoomID WHERE EscapeRoom.EscapeRoomID =@EscapeRoomID";
cmdReadERs.Parameters.AddWithValue("EscapeRoomID", ER_ID);
SqlDataReader reader = cmdReadERs.ExecuteReader();
if (reader.Read())
{
escapeRoom.escapeRoomID = reader.GetInt32(reader.GetOrdinal("EscapeRoomID"));
escapeRoom.name = reader.GetString(reader.GetOrdinal("EsName"));
escapeRoom.price = reader.GetDecimal(reader.GetOrdinal("Price"));
escapeRoom.maxClearTime = reader.GetInt32(reader.GetOrdinal("MaxClearTime"));
escapeRoom.cleanTime = reader.GetInt32(reader.GetOrdinal("CleanTime"));
escapeRoom.description = reader.GetString(reader.GetOrdinal("EsDescription"));
escapeRoom.rating = reader.GetDecimal(reader.GetOrdinal("Rating"));
escapeRoom.emp = DBemp.Get(reader.GetInt32(reader.GetOrdinal("EmployeeID")));
escapeRoom.Image = (byte[])reader.GetSqlBinary(reader.GetOrdinal("Image"));
int i = 0;
//while (reader.GetString(reader.GetOrdinal("CheckList")).Length >= i) {
// tempCheck = reader.GetString(reader.GetOrdinal("CheckList"));
// escapeRoom.AddToList(tempCheck.ToString());
// i++;
//}
}
}
}
return(escapeRoom);
}
