public static List <RoomTypeModel> GetAllRoomTypeModel() { List <RoomTypeModel> list = new List <RoomTypeModel>(); SqlConnection conn = Connection.GetConnection(); if (conn != null) { string sql = "select * from RoomType"; SqlCommand cm = new SqlCommand(sql, conn); var rs = cm.ExecuteReader(); if (rs.HasRows) { while (rs.Read()) { RoomTypeModel rt = new RoomTypeModel(); rt.RoomTypeID = rs.GetInt32(0); rt.RoomStyle = RoomStyleDAO.GetRoomStyle(rs.GetInt32(1)); rt.RoomSize = RoomSizeDAO.GetRoomSize(rs.GetInt32(2)); rt.Price = rs.GetInt32(3); rt.Description = rs.GetString(4); list.Add(rt); } } conn.Close(); } ; return(list); }
public static RoomTypeModel GetRoomTypeModel(int id) { RoomTypeModel rt = null; SqlConnection conn = Connection.GetConnection(); if (conn != null) { string sql = "select * from RoomType where RoomTypeID = @id"; SqlCommand cm = new SqlCommand(sql, conn); cm.Parameters.AddWithValue("@id", id); var rs = cm.ExecuteReader(); if (rs.HasRows) { if (rs.Read()) { rt = new RoomTypeModel(); rt.RoomTypeID = rs.GetInt32(0); rt.RoomStyle = RoomStyleDAO.GetRoomStyle(rs.GetInt32(1)); rt.RoomSize = RoomSizeDAO.GetRoomSize(rs.GetInt32(2)); rt.Price = rs.GetInt32(3); rt.Description = rs.GetString(4); } } conn.Close(); } ; return(rt); }
public static RoomsViewModel GetRoomsViewModel(List <string> Styles, List <string> Sizes) { RoomsViewModel model = new RoomsViewModel(); model.RoomStyles = RoomStyleDAO.GetAllRoomStyleViewModel(); model.RoomSizes = RoomSizeDAO.GetAllRoomSizeViewModel(); using (SqlConnection conn = Connection.GetConnection()) { if (conn != null) { string styles_formated = string.IsNullOrEmpty(string.Join(",", Styles)) ? ("0") : (string.Join(",", Styles)); string sizes_formated = string.IsNullOrEmpty(string.Join(",", Sizes)) ? ("0") : (string.Join(",", Sizes)); string sql = "select RoomID from Room, RoomType where Room.RoomTypeID = RoomType.RoomTypeID and RoomStyleID IN (" + styles_formated + ") and RoomSizeID IN (" + sizes_formated + ")"; SqlCommand cm = new SqlCommand(sql, conn); var rs = cm.ExecuteReader(); if (rs.HasRows) { while (rs.Read()) { RoomViewModel room = GetRoomViewModel(rs.GetInt32(0)); if (room.Status == "Empty") { model.EmptyCount++; } if (room.Status == "Reserved") { model.ReservedCount++; } if (room.Status == "Occupied") { model.OccupiedCount++; } if (room.Status == "Stayover") { model.StayoverCount++; } model.Rooms.Add(room); } } conn.Close(); } } return(model); }
public static RoomsViewModel GetRoomsViewModel() { RoomsViewModel model = new RoomsViewModel(); model.RoomStyles = RoomStyleDAO.GetAllRoomStyleViewModel(); model.RoomSizes = RoomSizeDAO.GetAllRoomSizeViewModel(); using (SqlConnection conn = Connection.GetConnection()) { if (conn != null) { string sql = "select RoomID from Room"; SqlCommand cm = new SqlCommand(sql, conn); var rs = cm.ExecuteReader(); if (rs.HasRows) { while (rs.Read()) { RoomViewModel room = GetRoomViewModel(rs.GetInt32(0)); if (room.Status == "Empty") { model.EmptyCount++; } if (room.Status == "Reserved") { model.ReservedCount++; } if (room.Status == "Occupied" || room.Status == "Stayover") { model.OccupiedCount++; } if (room.Status == "Stayover") { model.StayoverCount++; } model.Rooms.Add(room); } } conn.Close(); } } return(model); }
public static int InsertRoomStyle(string name, string shortname, string description) { int id = 0; using (SqlConnection conn = Connection.GetConnection()) { if (conn != null) { if (!RoomStyleDAO.CheckIsExistRoomStyleByName(name)) { SqlCommand cm = conn.CreateCommand(); cm.CommandText = "pro_CreateRoomStyle"; cm.CommandType = System.Data.CommandType.StoredProcedure; var rs = cm.ExecuteReader(); if (rs.HasRows) { rs.Read(); id = Decimal.ToInt32(rs.GetDecimal(0)); } conn.Close(); conn.Open(); if (id > 0) { string sql = "UPDATE RoomStyle SET RoomStyleName = @name, RoomStyleShortName = @shortname, Description = @description where RoomStyleID = @roomstyleid "; cm = new SqlCommand(sql, conn); cm.Parameters.AddWithValue("@roomstyleid", SqlDbType.Int).Value = id; cm.Parameters.AddWithValue("@name", SqlDbType.NVarChar).Value = name; cm.Parameters.AddWithValue("@shortname", SqlDbType.NVarChar).Value = shortname; cm.Parameters.AddWithValue("@description", SqlDbType.NVarChar).Value = description; int row = cm.ExecuteNonQuery(); if (row > 0) { return(id); } } } } } return(0); }