/// <summary>
/// Calculates aggregated progress data for all sets in the specified class.
/// If a non-null user is passed in, then the data is limited to that student's progress.
/// If a non-null set is passed in, then the data is limited to problems in that set.
/// </summary>
/// <param name="cls">The ClassData object with the class's id</param>
/// <param name="user">The UserData object with the user's id (optional)</param>
/// <param name="set">The ProblemSetData object with the set's id (optional)</param>
/// <returns>A non-null, possibly empty list of ProblemProgress objects</returns>
public List<ProblemProgress> GetProblemProgress(ClassData cls, UserData user = null, ProblemSetData set = null)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
List<ProblemProgress> list = new List<ProblemProgress>();
SqlCommand cmd = conn.CreateCommand();
string selectCols = "probs.Id, probs.Name";
StringBuilder query = new StringBuilder();
query.AppendLine("Select " + selectCols + ", ");
query.AppendLine(" Count(Case probs.IsCorrect When 1 Then 1 Else null End) as NumCorrect, ");
query.AppendLine(" Avg(Cast(probs.NumAttempts as float)) as AvgAttempts ");
query.AppendLine("from ( ");
query.AppendLine(" Select distinct p.*, s.IsCorrect, IsNull(s.NumAttempts, 0) as NumAttempts ");
query.AppendLine(" from dbo.[Problem] p ");
query.AppendLine(" Left Join dbo.[Solution] s on s.ProblemId = p.Id ");
query.AppendLine(" Join dbo.[ProblemSetProblem] psp on psp.ProblemId = p.Id ");
query.AppendLine(" Where p.ClassId = @clsId ");
if (user != null)
{
query.AppendLine(" and (s.UserId = @userId or s.UserId is null) ");
cmd.Parameters.AddWithValue("@userId", user.Id);
}
if (set != null)
{
query.AppendLine(" and psp.ProblemSetId = @setId ");
cmd.Parameters.AddWithValue("@setId", set.Id);
}
query.AppendLine(") probs ");
query.AppendLine("Group by " + selectCols);
//Class
cmd.Parameters.AddWithValue("@clsId", cls.Id);
cmd.CommandText = query.ToString();
SqlDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
while (reader.Read())
list.Add(createProblemProgressFromReader(reader));
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
if (reader != null)
reader.Close();
}
return list;
}
}
public void TestAddStudent()
{
ClassData cls = new ClassData(1);
UserData user = new UserData(8);
//This user should already be a student in the class
Assert.IsFalse(userModel.AddStudent(user, cls));
}
public void TestAdd()
{
ClassData cls = new ClassData(0);
cls.Name = "CS 4911 Design Capstone Project";
cls.Instructor = new UserData(7);
//This class should already exist in the database
Assert.IsFalse(classModel.Add(cls));
cls.Name = "Something new";
cls.Instructor = new UserData(0);
//This should fail because the Instructor Id is invalid
Assert.IsFalse(classModel.Add(cls));
}
/// <summary>
/// Calculates aggregated progress data for all students in the specified class.
/// </summary>
/// <param name="cls">The ClassData object with the class's id</param>
/// <returns>A non-null, possibly empty list of StudentProgress objects</returns>
public List<StudentProgress> GetStudentProgress(ClassData cls)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
List<StudentProgress> list = new List<StudentProgress>();
SqlCommand cmd = conn.CreateCommand();
string selectCols = "u.Id, u.Email, u.FirstName, u.LastName";
StringBuilder query = new StringBuilder();
query.AppendLine("Select " + selectCols + ", ");
query.AppendLine(" Count(Case s.IsCorrect When 1 Then 1 Else null End) as NumCorrect, ");
query.AppendLine(" Avg(Cast(s.NumAttempts as float)) as AvgAttempts ");
query.AppendLine("from dbo.[User] u ");
query.AppendLine("Join dbo.[Problem] p on p.ClassId = @clsId ");
query.AppendLine("Join dbo.[Solution] s on s.UserId = u.Id and s.ProblemId = p.Id ");
query.AppendLine("Group by " + selectCols);
//Class
cmd.Parameters.AddWithValue("@clsId", cls.Id);
cmd.CommandText = query.ToString();
SqlDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
if (reader.HasRows)
while (reader.Read())
list.Add(createStudentProgressFromReader(reader));
}
catch (Exception e)
{
Console.WriteLine(e);
}
finally
{
if (reader != null)
reader.Close();
}
return list;
}
}
public bool AddClass(ClassData newClass)
{
return classModel.Add(newClass);
}
public bool ModifyClass(ClassData cls)
{
return classModel.Modify(cls);
}
public ActionResult NewClass(ClassData newClass)
{
try
{
newClass.Instructor = new UserData(WebSecurity.CurrentUserId);
GlobalStaticVars.StaticCore.AddClass(newClass);
return RedirectToAction("Home", "Home");
}
catch (Exception e)
{
return RedirectToAction("ServerError", "Error");
}
}
/// <summary>
/// Calculates aggregated progress information for all students in the specified class.
/// </summary>
/// <param name="cls">The ClassData object with the class's id</param>
/// <returns>A non-null, possibly empty list of StudentProgress objects</returns>
public List<StudentProgress> GetStudentProgress(ClassData cls)
{
return progressDao.GetStudentProgress(cls);
}
/// <summary>
/// Calculates aggregated progress data for all sets in the specified class.
/// If a non-null user is passed in, then the data is limited to that student's progress.
/// </summary>
/// <param name="cls">The ClassData object with the class's id</param>
/// <param name="user">The UserData object with the user's id (optional)</param>
/// <returns></returns>
public List<SetProgress> GetSetProgress(ClassData cls, UserData user = null)
{
return progressDao.GetSetProgress(cls, user);
}
/// <summary>
/// Calculates aggregated progress data for all sets in the specified class.
/// If a non-null user is passed in, then the data is limited to that student's progress.
/// If a non-null set is passed in, then the data is limited to problems in that set.
/// </summary>
/// <param name="cls">The ClassData object with the class's id</param>
/// <param name="user">The UserData object with the user's id (optional)</param>
/// <param name="set">The ProblemSetData object with the set's id (optional)</param>
/// <returns>A non-null, possibly empty list of ProblemProgress objects</returns>
public List<ProblemProgress> GetProblemProgress(ClassData cls, UserData user = null, ProblemSetData set = null)
{
return progressDao.GetProblemProgress(cls, user, set);
}
/// <summary>
/// Calculates aggregated progress data for all students in the specified class.
/// If a non-null set is passed in, then the data is limited to problems in that set.
/// </summary>
/// <param name="cls">The ClassData object with the class's id</param>
/// <param name="set">The ProblemSetData object with the set's id (optional)</param>
/// <returns>A non-null, possibly empty list of StudentProgress objects</returns>
public List<StudentProgress> GetStudentProgress(ClassData cls, ProblemSetData set = null)
{
return progressDao.GetStudentProgress(cls, set);
}