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