public List <ClassAllocation> GetAllocatedClassInfo()
{
SqlConnection connection = new SqlConnection(connectionString);
string query = "SELECT * FROM ClassAllocation";
SqlCommand command = new SqlCommand(query, connection);
List <ClassAllocation> aList = new List <ClassAllocation>();
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
ClassAllocation aClassAllocationClass = new ClassAllocation();
aClassAllocationClass.Id = Convert.ToInt32(reader["Id"].ToString());
aClassAllocationClass.DepartmentId = Convert.ToInt32(reader["DepartmentId"].ToString());
aClassAllocationClass.CourseId = Convert.ToInt32(reader["CourseId"].ToString());
aClassAllocationClass.RoomId = Convert.ToInt32(reader["RoomId"].ToString());
aClassAllocationClass.DayId = Convert.ToInt32(reader["DayId"].ToString());
aClassAllocationClass.FromTimeHour = reader["FromTimeHour"].ToString();
aClassAllocationClass.FromTimeMinute = reader["FromTimeMinute"].ToString();
aClassAllocationClass.FromTimeFormat = reader["FromTimeFormat"].ToString();
aClassAllocationClass.ToTimeHour = reader["ToTimeHour"].ToString();
aClassAllocationClass.ToTimeMinute = reader["ToTimeMinute"].ToString();
aClassAllocationClass.ToTimeFormat = reader["ToTimeFormat"].ToString();
aList.Add(aClassAllocationClass);
}
reader.Close();
}
connection.Close();
return(aList);
}
public StudentInfoOutput AddToClass([FromBody] StudentInfoInput input)
{
StudentInfoOutput output = new StudentInfoOutput();
if (input == null)
{
Response.StatusCode = 400;
output.Result = "INPUT_IS_NULL";
}
else
{
Class thisClass = _db.Classes.Where(e => e.ClassCode.ToUpper().Equals(input.ClassCode.ToUpper()) && e.Deleted == false).FirstOrDefault();
if (thisClass == null)
{
Response.StatusCode = 400;
output.Result = "CLASS_NOT_EXIST";
}
else
{
AspUserService aspUser = new AspUserService(_db, this);
if (aspUser.IsAdmin)
{
BCPUser student = _db._BCPUsers.Where(e => e.Id.Equals(input.StudentId) && e.Deleted == false).FirstOrDefault();
if (student == null)
{
Response.StatusCode = 400;
output.Result = "STUDENT_NOT_EXIST";
}
else
{
ClassAllocation ca = _db.ClassAllocations.Where(e => e.Class == thisClass && e.Student == student && e.Deleted == false).FirstOrDefault();
if (ca == null)
{
ClassAllocation newCa = new ClassAllocation()
{
Class = thisClass,
Student = student
};
_db.ClassAllocations.Add(newCa);
_db.SaveChanges();
output.Result = "OK";
}
else
{
Response.StatusCode = 400;
output.Result = "ALREAD_ADDED";
}
}
}
else
{
Response.StatusCode = 400;
output.Result = "NO_PRIVILEGE";
}
}
}
return(output);
}
public ActionResult CLassroomAllocation(ClassAllocation aClassAllocation)
{
DepartmentManager aDepartmentManager = new DepartmentManager();
CourseManager aCourseManager = new CourseManager();
RoomManager aRoomManager = new RoomManager();
DayManager aDayManager = new DayManager();
ViewBag.departments = aDepartmentManager.GetAllDepartmentInfo();
ViewBag.courses = aCourseManager.GetAllCourse();
ViewBag.rooms = aRoomManager.GetAllRoom();
ViewBag.days = aDayManager.GetAllDay();
ClassAllocationManager aClassAllocationManager = new ClassAllocationManager();
List <ClassAllocation> alist = aClassAllocationManager.GetAllocatedClassInfo();
if (aClassAllocationManager.CLassroomAllocation(aClassAllocation) > 0)
{
ViewBag.message = "Classrooms are Successfully allocated";
}
else
{
ViewBag.message = "Allocation Failed";
}
return(View());
}
public ActionResult AllocateClassrooms(ClassAllocation classroomAllocation, int fromHour, int fromMin, string fromAmPm, int toHour, int toMin, string toAmPm)
{
List <SelectListItem> aDepartments = departmentManager.GetAllDepartment();
ViewBag.departments = aDepartments;
List <SelectListItem> aRoomNos = classAllocationManager.GetAllRoomNos();
ViewBag.RoomNo = aRoomNos;
List <SelectListItem> daysListItems = classAllocationManager.GetAllDays();
ViewBag.Day = daysListItems;
classroomAllocation.From = ConvertTime(fromHour, fromMin, fromAmPm);
classroomAllocation.To = ConvertTime(toHour, toMin, toAmPm);
string message = classAllocationManager.SaveClassroomAllocationInfo(classroomAllocation);
ViewBag.message = message;
if (ViewBag.message == "invalid time")
{
ViewBag.From = GetTimeString(classroomAllocation.From);
ViewBag.To = GetTimeString(classroomAllocation.To);
}
ModelState.Clear();
return(View());
}
public int SaveClassAllocation(ClassAllocation classroomAllocation)
{
Query = "Insert Into ClassAllocation Values ('" + classroomAllocation.DepartmentId + "','" + classroomAllocation.CourseId + "','" + classroomAllocation.RoomNo + "','" + classroomAllocation.Day + "','" + classroomAllocation.From + "','" + classroomAllocation.To + "','1')";
Command = new SqlCommand(Query, Connection);
Connection.Open();
int rowAffected = Command.ExecuteNonQuery();
Connection.Close();
return(rowAffected);
}
public int CLassroomAllocation(ClassAllocation aClassAllocation)
{
SqlConnection connection = new SqlConnection(connectionString);
//string query = "INSERT INTO ClassAllocation (DepartmentId,CourseId,RoomId,DayId,FromTime,ToTime) VALUES (@DepartmentId,@CourseId,@RoomId,@DayId,@FromTime,@ToTime)";
string query = "INSERT INTO ClassAllocation (DepartmentId,CourseId,RoomId,DayId,FromTimeHour,FromTimeMinute,FromTimeFormat,ToTimeHour,ToTimeMinute,ToTimeFormat) VALUES ('" + aClassAllocation.DepartmentId + "','" + aClassAllocation.CourseId + "','" + aClassAllocation.RoomId + "','" + aClassAllocation.DayId + "','" + aClassAllocation.FromTimeHour + "','" + aClassAllocation.FromTimeMinute + "','" + aClassAllocation.FromTimeFormat + "','" + aClassAllocation.ToTimeHour + "','" + aClassAllocation.ToTimeMinute + "','" + aClassAllocation.ToTimeFormat + "')";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
connection.Close();
return(rowsAffected);
}
public string SaveClassroomAllocationInfo(ClassAllocation classroomAllocation)
{
if (classAllocationGateway.IsClassRoomAlreadyAllocated(classroomAllocation))
{
return("conflict");
}
if (classroomAllocation.To < classroomAllocation.From)
{
return("invalid time");
}
int rowAffected = classAllocationGateway.SaveClassAllocation(classroomAllocation);
if (rowAffected > 0)
{
return("yes");
}
else
{
return("no");
}
}
public bool IsClassRoomAlreadyAllocated(ClassAllocation classroomAllocation)
{
Query = "select * from ClassAllocation Where ((Day =@day And RoomNo=@roomNo) OR (Day =@day " +
"And CourseId=@courseId))" +
"And " +
"((@fromTime >=FromTime AND @fromTime<ToTime) OR (@toTime>FromTime AND @toTime<=ToTime) OR" +
" (@fromTime<=FromTime AND @toTime>=ToTime)) And Flag = '1'";
Command = new SqlCommand(Query, Connection);
Command.Parameters.AddWithValue("@day", classroomAllocation.Day);
Command.Parameters.AddWithValue("@roomNo", classroomAllocation.RoomNo);
Command.Parameters.AddWithValue("@courseId", classroomAllocation.CourseId);
Command.Parameters.AddWithValue("@fromTime", classroomAllocation.From);
Command.Parameters.AddWithValue("@toTime", classroomAllocation.To);
Connection.Open();
Reader = Command.ExecuteReader();
bool Bool = Reader.HasRows;
Connection.Close();
return(Bool);
}
public int CLassroomAllocation(ClassAllocation aClassAllocation)
{
return(aClassAllocationGetway.CLassroomAllocation(aClassAllocation));
}
