/// <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 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); }