/*
* Student registration number generator
*/
public string RegistrationNo(string RegPart)
{
int total = new UoUDBContext().Database.SqlQuery <int>("SELECT COUNT(StudentRegNo) FROM dbo.Students WHERE StudentRegNo LIKE @RegPart", new SqlParameter("RegPart", RegPart + "%")).FirstOrDefault();
string regNo = "000" + (total + 1);
return(string.Format("{0}{1}", RegPart, regNo.Substring(regNo.Length - 4)));
}
public async Task <ActionResult> Index()
{
string query = "SELECT DeptCode, DeptName, " +
"(SELECT count(*) FROM courses c WHERE c.CourseDeptId = d.deptId) AS 'TotalCourses', " +
"(SELECT count(*) FROM Teachers t WHERE t.teacherDeptId = d.deptId) AS 'TotalTeachers' " +
"FROM departments d order by DeptCode";
var departments = new UoUDBContext().Database.SqlQuery <DepartmentStatics>(query).ToListAsync();
return(View(await departments));
}
public bool UnallocateRooms()
{
UoUDBContext db = new UoUDBContext();
foreach (var classroom in db.RoomAllocations.Where(x => x.RecordStatus == 1).ToList())
{
classroom.RecordStatus = 0;
db.Entry(classroom).State = EntityState.Modified;
}
if (db.SaveChanges() > 0)
{
return(true);
}
return(false);
}
public bool UnassignCourses()
{
UoUDBContext db = new UoUDBContext();
foreach (var teacher_course in db.TeacherCourses.Where(x => x.RecordStatus == 1).ToList())
{
teacher_course.RecordStatus = 0;
db.Entry(teacher_course).State = EntityState.Modified;
}
foreach (var student_course in db.StudentCourses.Where(x => x.RecordStatus == 1).ToList())
{
student_course.RecordStatus = 0;
db.Entry(student_course).State = EntityState.Modified;
}
if (db.SaveChanges() > 0)
{
return(true);
}
return(false);
}
public bool IsTimeConflict(int dayId, int roomId, string fromTime, string toTime)
{
var from = "000" + (Convert.ToInt32(fromTime.Replace(":", "")) + 1);
var to = "000" + (Convert.ToInt32(toTime.Replace(":", "")) - 1);
string query = "SELECT * FROM RoomAllocations " +
"WHERE RoomAllocationDayId = @dayId " +
"AND RoomAllocationRoomId = @roomId " +
"AND RecordStatus = 1 " +
"AND((@from BETWEEN FromTime AND ToTime) OR(@to BETWEEN FromTime AND ToTime)) " +
"OR ((FromTime BETWEEN @from AND @to) OR (ToTime BETWEEN @from AND @to))";
var data = new UoUDBContext().Database.SqlQuery <RoomAllocationModel>(
query,
new SqlParameter("dayId", dayId),
new SqlParameter("roomId", roomId),
new SqlParameter("from", from.Substring(from.Length - 4)),
new SqlParameter("to", to.Substring(to.Length - 4))).ToList();
if (data.Count > 0)
{
return(false);
}
return(true);
}
public List <CourseModel> GetCoursesByStudentId(int studentId)
{
var studentCourses = new UoUDBContext().StudentCourses.Where(x => x.StudentCourseStudentId == studentId && x.RecordStatus == 1).Select(y => y.StudentCourseCourseId).ToList();
return(new UoUDBContext().Courses.Where(x => studentCourses.Contains(x.CourseId)).OrderBy(y => y.CourseCode).ToList());
}
