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