public List <SlotModel> GetSlotsByDate(DateTime date)
{
List <SlotModel> slots = new List <SlotModel>();
SqlConnection conn = new SqlConnection(Constants.ConnectionString);
var dateStr = date.ToString("yyyy-MM-dd");
SqlCommand query = new SqlCommand($"select * from Slot WHERE StartTime >= '{dateStr} 00:00:00' AND StartTime <= '{dateStr} 23:59:59'", conn);
SqlDataReader read;
try
{
conn.Open();
read = query.ExecuteReader();
while (read.Read())
{
StaffController staffController;
UserModel staffModel;
staffController = new StaffController();
staffModel = staffController.GetUser(read["StaffID"].ToString());
if (!String.IsNullOrWhiteSpace(read["BookedInStudentID"].ToString()))
{
UserModel studentModel;
StudentController studentController = new StudentController();
studentModel = studentController.GetUser(read["BookedInStudentID"].ToString());
slots.Add(new SlotModel(new RoomModel(read["RoomID"].ToString()[0]), Convert.ToDateTime(read["StartTime"]), staffModel, studentModel));
}
else
{
slots.Add(new SlotModel(new RoomModel(read["RoomID"].ToString()[0]), Convert.ToDateTime(read["StartTime"]), staffModel));
}
}
read.Close();
}
catch (SqlException se)
{
Console.WriteLine("SQL Exception: {0}", se.Message);
}
catch (Exception e)
{
Console.WriteLine("Exception: {0}", e.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(slots);
}
public SlotModel GetSlot(string roomId, DateTime dateTime)
{
SlotModel slot = null;
var dateStr = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
SqlConnection conn = new SqlConnection(Constants.ConnectionString);
SqlCommand query = new SqlCommand($"SELECT * from Slot WHERE RoomID = '{roomId}' AND StartTime = '{dateStr}';", conn);
SqlDataReader read;
try
{
conn.Open();
read = query.ExecuteReader();
while (read.Read())
{
StaffController staffController;
UserModel staffModel;
staffController = new StaffController();
staffModel = staffController.GetUser(read["StaffID"].ToString());
if (!String.IsNullOrWhiteSpace(read["BookedInStudentID"].ToString()))
{
UserModel studentModel;
StudentController studentController = new StudentController();
studentModel = studentController.GetUser(read["BookedInStudentID"].ToString());
slot = new SlotModel(new RoomModel(read["RoomID"].ToString()[0]), Convert.ToDateTime(read["StartTime"]), staffModel, studentModel);
}
else
{
slot = new SlotModel(new RoomModel(read["RoomID"].ToString()[0]), Convert.ToDateTime(read["StartTime"]), staffModel);
}
}
read.Close();
}
catch (SqlException se)
{
Console.WriteLine("SQL Exception: {0}", se.Message);
}
catch (Exception e)
{
Console.WriteLine("Exception: {0}", e.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(slot);
}