public List <ViewClassSchedule> GetClassSchedules(int deptId)
{
string query = " select sc.Code, sc.CourseName, r.RoomNo,ar.Day,ar.FromHour,ar.FromMin,ar.FromFormat,ar.ToHour,ar.ToMin,ar.ToFormat,ar.Assign from Course sc left join AllocateRoom ar ON ar.CourseId = sc.Id left join Room r On ar.RoomId = r.id where sc.Id IN(select ID from Course Where DepartmentId = '" + deptId + "' ) ";
Connection.Open();
Command.CommandText = query;
SqlDataReader reader = Command.ExecuteReader();
List <ViewClassSchedule> classSchedules = new List <ViewClassSchedule>();
while (reader.Read())
{
ViewClassSchedule classSchedule = new ViewClassSchedule()
{
CourseCode = reader["Code"] as string,
CourseName = reader["CourseName"] as string,
RoomNo = reader["RoomNo"] as string,
Day = reader["Day"] as string,
FromHour = (reader["FromHour"] as int?) ?? 0,
FromMin = (reader["FromMin"] as int?) ?? 0,
FromFormat = reader["FromFormat"] as string,
ToHour = (reader["ToHour"] as int?) ?? 0,
ToMin = (reader["ToMin"] as int?) ?? 0,
ToFormat = reader["ToFormat"] as string,
Assign = reader["Assign"] as string
};
classSchedules.Add(classSchedule);
}
Connection.Close();
reader.Close();
return(classSchedules);
}
public List <ViewClassSchedule> GetClassScheduleByDeptId(int departmentId)
{
List <ViewClassSchedule> aList = new List <ViewClassSchedule>();
Query = "SELECT Code, Name, DepartmentId, RoomNumber, Days, CONVERT(varchar,FromTime,100) AS FromTime, CONVERT(varchar,ToTime,100) AS ToTime, Valid FROM ViewClassSchedule WHERE DepartmentId=" + departmentId;
Command = new SqlCommand(Query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
while (Reader.Read())
{
ViewClassSchedule s = new ViewClassSchedule
{
Code = Reader["Code"].ToString(),
Name = Reader["Name"].ToString(),
DepartmentId = (int)Reader["DepartmentId"],
RoomNo = Reader["RoomNumber"].ToString(),
Days = Reader["Days"].ToString(),
FromTime = Reader["FromTime"].ToString(),
ToTime = Reader["ToTime"].ToString(),
Valid = Reader["Valid"].ToString()
};
//if (Reader["Valid"].ToString()=="True")
//{
// string str = "";
// s.RoomNo = Reader["RoomNumber"].ToString();
// s.Days = Reader["Days"].ToString();
// s.FromTime = Reader["FromTime"].ToString();
// s.ToTime = Reader["ToTime"].ToString();
// str += "Room No.: " + s.RoomNo + ", " + s.Days + ", " + s.FromTime + " - " +s.ToTime + ";<br/>";
// s.ScheduleInfo = str;
//}
//else
//{
// s.ScheduleInfo = "Not Scheduled Yet <br/>";
//}
aList.Add(s);
}
Reader.Close();
Connection.Close();
return(aList);
}
public PartialViewResult FilterView(int?departmentId)
{
if (departmentId != null)
{
var dptCors = db.Courses.Where(d => d.DepartmentId == departmentId);
List <ViewClassSchedule> clssch = new List <ViewClassSchedule>();
foreach (Course course in dptCors)
{
ViewClassSchedule aViewClassSchedule = new ViewClassSchedule();
aViewClassSchedule.CourseName = course.Name;
aViewClassSchedule.CourseCode = course.Code;
aViewClassSchedule.ScheduleInfo = "";
var al =
db.AllocateClassRooms.Include(a => a.Department)
.Include(a => a.Course)
.Include(a => a.ClassRoom)
.Include(a => a.WeekDay)
.Where(a => a.CourseId == course.CourseId);
foreach (var all in al)
{
aViewClassSchedule.ScheduleInfo += "Room No: " + all.ClassRoom.RoomNo + " " + all.WeekDay.Day +
" " +
all.StarTimeHour + ":" + all.StarTimeMin + "-" +
all.EndTimeHour +
":" + all.EndTimeMin + ";";
}
clssch.Add(aViewClassSchedule);
}
return(PartialView("~/Views/Shared/_ViewClassScheduleAndAllocation.cshtml", clssch));
}
else
{
List <ViewClassSchedule> clssch = new List <ViewClassSchedule>();
ViewBag.message = "Select a department";
return(PartialView("~/Views/Shared/_ViewClassScheduleAndAllocation.cshtml", clssch));
}
}
public List <ViewClassSchedule> GetClassScheduleByDeptId(int departmentId)
{
//return aClassRoomGateway.GetClassScheduleByDeptId(departmentId);
List <ViewClassSchedule> aList = new List <ViewClassSchedule>();
var allocate = aClassRoomGateway.GetClassScheduleByDeptId(departmentId).OrderBy(m => m.Code).ThenBy(m => m.Days).ThenBy(m => m.FromTime).ToList();
var aDistinctCode = allocate.DistinctBy(m => m.Code).ToList();
string str = "";
foreach (var a in aDistinctCode)
{
ViewClassSchedule aSchedule = new ViewClassSchedule();
aSchedule.Code = a.Code;
aSchedule.Name = a.Name;
aSchedule.DepartmentId = a.DepartmentId;
foreach (var v in allocate)
{
if (a.Code == v.Code)
{
str += "Room No.: " + v.RoomNo + ", " + v.Days + ", " + v.FromTime + " - " +
v.ToTime + ";<br/>";
}
}
if (a.Valid != "True")
{
str = "Not Scheduled Yet" + "<br/>";
}
aSchedule.ScheduleInfo = str;
str = "";
aList.Add(aSchedule);
}
return(aList);
}
