public DataTable MakeStudentScoreResultTable()
{
try
{
SqlCommand command = new SqlCommand("select * from Score", data.GetConnection);
Students student = new Students();
Courses course = new Courses();
Scores score = new Scores();
//create column
DataTable result = student.GetAllBriefInfo();
result.Columns[0].ColumnName = "ID";
result.Columns[1].ColumnName = "Firt Name";
result.Columns[2].ColumnName = "Last Name";
DataTable coursesLabel = course.AllLabel_IdOrder();
DataTable scores = score.GetALL_IdCourseOrder();
DataTable avgScore = score.GetAvg_byStudent();
for (int i = 0; i < coursesLabel.Rows.Count; i++)
{
result.Columns.Add(coursesLabel.Rows[i][1].ToString().Trim());
}
result.Columns.Add("Average");
result.Columns.Add("Result");
//test empty
if (scores.Rows.Count < 1)
{
return(result);
}
//fill score to table
int scoreRow = 0;
for (int row = 0; row < result.Rows.Count; row++)
{
try
{
int courseIndex = 0;
while (result.Rows[row][0].ToString().Trim() == scores.Rows[scoreRow][0].ToString().Trim())
{
result.Rows[row][courseIndex + 3] = scores.Rows[scoreRow][2].ToString().Trim();
courseIndex++;
scoreRow++;
if (scoreRow > scores.Rows.Count - 1)
{
break;
}
}
}
catch (Exception)
{
continue;
}
}
// fill avg score
for (int row = 0; row < result.Rows.Count; row++)
{
try
{
result.Rows[row][result.Columns.Count - 2]
= avgScore.Rows[row][1].ToString().Trim();
float avg = (float)Convert.ToDouble(avgScore.Rows[row][1]);
if (avg < 4.99999)
{
result.Rows[row][result.Columns.Count - 1] = "Fail";
}
else
{
{
result.Rows[row][result.Columns.Count - 1] = "Pass";
}
}
}
catch (Exception)
{
continue;
}
}
//DataTable table = score.GetScore(command);
//for (int row = 0; row < result.Rows.Count; row++)
//{
// try
// {
// result.Rows[row][result.Columns.Count - 1]
// = table.Rows[row][3].ToString().Trim();
// }
// catch (Exception)
// {
// continue;
// }
//}
return(result);
}
catch (Exception)
{
throw;
}
}
