public IEnumerable <StudentResultViewModel> GetStudentResultByStudentId(int id)
{
try
{
List <StudentResultViewModel> studentResults = new List <StudentResultViewModel>();
CommandObj.CommandText = "spGetStudentResult";
CommandObj.CommandType = CommandType.StoredProcedure;
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@studentId", id);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
StudentResultViewModel studentResult = new StudentResultViewModel
{
StudentId = Convert.ToInt32(reader["StudentId"].ToString()),
Code = reader["Code"].ToString(),
Name = reader["Name"].ToString(),
Grade = reader["Grade"].ToString()
};
studentResults.Add(studentResult);
}
reader.Close();
return(studentResults);
}
catch (Exception exception)
{
throw new Exception("Unable to collect sudent result", exception);
}
finally
{
CommandObj.Dispose();
ConnectionObj.Close();
}
}
public List <StudentResultViewModel> GetRegNoByStudentResultInfo(int RegistrationNo)
{
string query = "SELECT Student.Name AS StudentName,Student.Email,Department.Name AS DepartmentName," +
"Course.Code,Course.Name AS CourseName,ISNULL(GradeLetter.GradeLetter,'Not Graded Yet') " +
"AS Grade FROM EnrollCourse INNER JOIN Student ON EnrollCourse.RegistrationNo=Student.Id " +
"INNER JOIN Course ON EnrollCourse.CourseId=Course.Id INNER JOIN Department" +
" ON Course.DepartmentId=Department.Id LEFT JOIN StudentResult ON " +
"EnrollCourse.CourseId=StudentResult.CourseId LEFT JOIN GradeLetter " +
"ON StudentResult.GradeLetterId=GradeLetter.Id WHERE EnrollCourse.RegistrationNo=" + RegistrationNo + "";
Command = new SqlCommand(query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
List <StudentResultViewModel> studentResultViews = new List <StudentResultViewModel>();
while (Reader.Read())
{
StudentResultViewModel studentResultView = new StudentResultViewModel();
studentResultView.Name = Reader["StudentName"].ToString();
studentResultView.Email = Reader["Email"].ToString();
studentResultView.DepartmentName = Reader["DepartmentName"].ToString();
studentResultView.CourseCode = Reader["Code"].ToString();
studentResultView.CourseName = Reader["CourseName"].ToString();
studentResultView.GradeLetter = Reader["Grade"].ToString();
studentResultViews.Add(studentResultView);
}
Connection.Close();
return(studentResultViews);
}
public IEnumerable <StudentResultViewModel> GetStudentResultByStudentId(int id)
{
List <StudentResultViewModel> studentResults = new List <StudentResultViewModel>();
Query = "spGetStudentResult";
SqlCommand Command = new SqlCommand(Query, Connection);
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.AddWithValue("@studentId", id);
Connection.Open();
SqlDataReader reader = Command.ExecuteReader();
while (reader.Read())
{
StudentResultViewModel studentResult = new StudentResultViewModel
{
StudentId = Convert.ToInt32(reader["StudentId"].ToString()),
Code = reader["Code"].ToString(),
Name = reader["Name"].ToString(),
Grade = reader["Grade"].ToString()
};
studentResults.Add(studentResult);
}
reader.Close();
return(studentResults);
}
public List <StudentResultViewModel> GetAllCourseResult(int studentId)
{
Query = "Select c.Code, c.Name, ce.Grade from EnrollCourse ce inner join Course c on c.Id = ce.CourseId where ce.StudentId =" + studentId + " AND ce.Assign = '1'";
Command = new SqlCommand(Query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
List <StudentResultViewModel> studentResults = new List <StudentResultViewModel>();
while (Reader.Read())
{
StudentResultViewModel studentResult = new StudentResultViewModel();
studentResult.CourseCode = Reader["Code"].ToString();
studentResult.CourseName = Reader["Name"].ToString();
studentResult.Grade = Reader["Grade"].ToString();
if (studentResult.Grade == "")
{
studentResult.Grade = "Not Graded Yet";
}
studentResults.Add(studentResult);
}
Reader.Close();
Connection.Close();
return(studentResults);
}
public List <StudentResultViewModel> GetStudentResult(StudentResult studentResult)
{
Command.CommandText = @"select c.Code as CourseCode,c.Name as CourseName,ISNULL(g.Name,'Not Graded Yet') as GradeName from StudentEnroleCourse sec left join Course c on sec.CourseId=c.Id left join StudentResult sr on sr.CourseId=c.Id left join Grade g on sr.GradeId=g.Id where sec.StudentId='" + studentResult.StudentId + "'";
Connection.Open();
SqlDataReader reader = Command.ExecuteReader();
List <StudentResultViewModel> studentResultViewModels = new List <StudentResultViewModel>();
StudentResultViewModel srv = null;
if (reader.HasRows)
{
while (reader.Read())
{
srv = new StudentResultViewModel()
{
CourseCode = reader["CourseCode"].ToString(),
CourseName = reader["CourseName"].ToString(),
GradeName = reader["GradeName"].ToString(),
};
studentResultViewModels.Add(srv);
}
}
reader.Close();
Connection.Close();
return(studentResultViewModels);
}
public List <StudentResultViewModel> GetStudentResultByStudentId(int id)
{
List <StudentResultViewModel> studentResults = new List <StudentResultViewModel>();
CommandObj.CommandText = "SELECT en.StudentId, c.Code,c.Name,COALESCE(r.Grade,'Not Graded yet') as Grade FROM StudentResult_tbl r RIGHT OUTER JOIN ( SELECT e.Id,e.StudentId,e.CourseId FROM StudentEnrollInCourse_tbl e WHERE e.StudentId=@studentId AND e.IsStudentActive=1) en ON r.CourseId=en.CourseId AND r.StudentId=en.StudentId AND r.IsStudentActive=1 INNER JOIN Course_tbl c ON en.CourseId=c.Id";
CommandObj.Parameters.Clear();
CommandObj.Parameters.AddWithValue("@studentId", id);
ConnectionObj.Open();
SqlDataReader reader = CommandObj.ExecuteReader();
while (reader.Read())
{
StudentResultViewModel studentResult = new StudentResultViewModel
{
StudentId = Convert.ToInt32(reader["StudentId"].ToString()),
Code = reader["Code"].ToString(),
Name = reader["Name"].ToString(),
Grade = reader["Grade"].ToString()
};
studentResults.Add(studentResult);
}
reader.Close();
CommandObj.Dispose();
ConnectionObj.Close();
return(studentResults);
}
public IActionResult Create(StudentResultViewModel studentResultViewModel)
{
var studentResult = _mapper.Map <StudentResult>(studentResultViewModel);
if (ModelState.IsValid)
{
try
{
bool isSaved = _studentResultManager.Add(studentResult);
if (isSaved)
{
ViewBag.Message = "Result Saved To Student Successfully";
}
else
{
ViewBag.Message = "Result Save Failled";
}
}
catch (Exception e)
{
ViewBag.GradeExistMessage = e.Message;
}
}
ViewBag.StudentId = new SelectList(_studentManager.GetAll(), "StudentId", "RegNo");
ViewBag.GradePointId = new SelectList(_gradePointManager.GetAll(), "GradePointId", "GradeName");
return(View());
}
public async Task GetPerPageByStudentIdAsyncShouldSkipCorrectly()
{
var studentId = await this.CreateStudentAsync();
var firstEventDate = DateTime.UtcNow;
var secondEventDate = DateTime.UtcNow;
var firstEventInfo = await this.CreateEventAsync("First Event", firstEventDate);
var secondEventInfo = await this.CreateEventAsync("Second Event", secondEventDate);
await this.CreateResultAsync(studentId, 2, 10, firstEventInfo[0]);
await this.CreateResultAsync(studentId, 5, 10, secondEventInfo[0]);
var firstModel = new StudentResultViewModel()
{
Event = "First Event",
Quiz = "quiz",
EventActivationDateAndTime = firstEventDate,
Score = "2/10",
};
var resultModelCollection = await this.Service.GetPerPageByStudentIdAsync <StudentResultViewModel>(studentId, 2, 1);
Assert.Single(resultModelCollection);
Assert.Equal(firstModel.Event, resultModelCollection.First().Event);
Assert.Equal(firstModel.Quiz, resultModelCollection.First().Quiz);
Assert.Equal(firstModel.Date, resultModelCollection.First().Date);
Assert.Equal(firstModel.Score, resultModelCollection.First().Score);
}
public async Task AddRating_RecordShouldBeAddedToDb()
{
var model = new StudentResultViewModel {
Rating = 5, StudentId = Guid.NewGuid()
};
var result = await serviceUnderTest.AddRating(model);
Assert.NotEmpty(dbFixture.DbContext.StudentsResults.ToList());
}
public async Task <int> AddRating(StudentResultViewModel model)
{
var studentResultEntity = new StudentResult()
{
StudentId = model.StudentId,
Rating = model.Rating
};
_context.StudentsResults.Add(studentResultEntity);
return(await _context.SaveChangesAsync());
}
public async Task Post_ResultShouldNotBeSavedInDb()
{
var model = new StudentResultViewModel {
Rating = 5, StudentId = Guid.Empty
};
var response =
await integrationFixture.Client.PostAsync(endpoint, new StringContent(JsonConvert.SerializeObject(model), Encoding.UTF8, "application/json"));
Assert.True(response.IsSuccessStatusCode);
Assert.Equal(0, dbFixture.DbContext.StudentsResults.Where(r => r.StudentId == model.StudentId).Count());
}
public async Task <IActionResult> Post([FromBody] StudentResultViewModel model)
{
if (model.StudentId == Guid.Empty)
{
return(Json(true));
}
await _context.AddRating(model);
return(Json(true));
//10.06 -> następny do obejrzenia
}
public void Insert(StudentResultViewModel model)
{
if (model == null)
{
throw new ArgumentNullException("StudentResult");
}
StudentResult studentResult = new StudentResult
{
StudentRegId = model.StudentRegId,
CourseId = model.CourseId,
Grade = model.Grade
};
entities.Add(studentResult);
_context.SaveChanges();
}
public bool CheckExist(StudentResultViewModel aStudentResultViewModel)
{
Result aResult =
aUnitOfWork.Repository <Result>()
.GetModel(
x =>
x.StudentId == aStudentResultViewModel.StudentId &&
x.CourseId == aStudentResultViewModel.CourseId && x.IsActive == true);
if (aResult == null)
{
return(false);
}
else
{
return(true);
}
}
public List <StudentResultViewModel> GetRegNoByStudentInfo(int RegistrationNo)
{
string query = "SELECT Student.Id,Student.Name AS StudentName,Student.Email,Department.Id AS DepartmentId,Department.Name AS DepartmentName FROM Student INNER JOIN Department ON Student.DepartmentId=Department.Id WHERE Student.Id=" + RegistrationNo + "";
Command = new SqlCommand(query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
List <StudentResultViewModel> studentResultViews = new List <StudentResultViewModel>();
if (Reader.Read())
{
StudentResultViewModel studentResultView = new StudentResultViewModel();
studentResultView.Id = Convert.ToInt32(Reader["Id"]);
studentResultView.Name = Reader["StudentName"].ToString();
studentResultView.Email = Reader["Email"].ToString();
studentResultView.DepartmentId = Convert.ToInt32(Reader["DepartmentId"]);
studentResultView.DepartmentName = Reader["DepartmentName"].ToString();
studentResultViews.Add(studentResultView);
}
Connection.Close();
return(studentResultViews);
}
public List <StudentResultViewModel> GetStudentResultInfoByRegNo(int registrationNo)
{
string query = "SELECT Student.Name AS StudentName,Student.Email,Department.Name AS DepartmentName,Course.Code,Course.Name AS CourseName,Grade.Name FROM StudentResult INNER JOIN Student ON StudentResult.RegistrationNo=Student.Id INNER JOIN Course ON StudentResult.CourseId=Course.Id INNER JOIN Grade ON StudentResult.GradeId=Grade.Id INNER JOIN Department ON Course.DepartmentId=Department.Id WHERE StudentResult.RegistrationNo=" + registrationNo + "";
Command = new SqlCommand(query, Connection);
Connection.Open();
Reader = Command.ExecuteReader();
List <StudentResultViewModel> studentResultViews = new List <StudentResultViewModel>();
while (Reader.Read())
{
StudentResultViewModel studentResultView = new StudentResultViewModel();
studentResultView.Name = Reader["StudentName"].ToString();
studentResultView.Email = Reader["Email"].ToString();
studentResultView.DepartmentName = Reader["DepartmentName"].ToString();
studentResultView.CourseCode = Reader["Code"].ToString();
studentResultView.CourseName = Reader["CourseName"].ToString();
studentResultView.GradeLetter = Reader["Name"].ToString();
studentResultViews.Add(studentResultView);
}
Connection.Close();
return(studentResultViews);
}
public string SaveResult(StudentResultViewModel aStudentResultViewModel)
{
Result aResult = new Result();
aResult.StudentId = aStudentResultViewModel.StudentId;
aResult.CourseId = aStudentResultViewModel.CourseId;
aResult.Grade = aStudentResultViewModel.Grade;
aResult.IsActive = true;
if (CheckExist(aStudentResultViewModel))
{
Result aResult2 =
aUnitOfWork.Repository <Result>()
.GetModel(
x => x.StudentId == aResult.StudentId && x.CourseId == aResult.CourseId && x.IsActive == true);
aResult2.Grade = aResult.Grade;
bool flag = aUnitOfWork.Repository <Result>().UpdateModel(aResult2);
aUnitOfWork.Save();
if (flag)
{
return("Resullt Updated");
}
}
bool flag2 = aUnitOfWork.Repository <Result>().InsertModel(aResult);
aUnitOfWork.Save();
if (flag2)
{
return("Result Saved Succesfully");
}
else
{
return("Result saving failed");
}
}
private StudentResultViewModel StudentResult(int studentId)
{
//Student With Id
var aStudent = _studentManager.GetAllStudents().FirstOrDefault(s => s.Id == studentId);
//Brought the student course grade form results table
var studentPublishedResult = _resultManager.GetAllStudentResults().Where(r => r.StudentId == studentId).Select(r =>
new
{
CourseId = r.CourseId,
CourseCode = r.Course.CourseCode,
CourseName = r.Course.CourseName,
Grade = r.Grade
}).ToList();
//Brougnt all the courses assigned to that student
var studentAllCourse = _courseManager.GetAllStudentCourses().Where(c => c.StudentId == studentId).Select(c => new
{
CourseId = c.Course.Id,
CourseName = c.Course.CourseName,
CourseCode = c.Course.CourseCode
}).ToList();
//Selecting not published results course id from course table
var studentCourseWithoutResultId = studentAllCourse.Select(c => new { CourseId = c.CourseId })
.Except(studentPublishedResult.Select(p => new { CourseId = p.CourseId })).ToList();
//Joining not published results course id with all course to get the course code course name and assiging grad
var studentCourseWithoutResult = studentCourseWithoutResultId.Join(studentAllCourse, c => c.CourseId,
a => a.CourseId, (ci, ac) => new
{
CourseId = ac.CourseId,
CourseCode = ac.CourseCode,
CourseName = ac.CourseName,
Grade = "Not Graded yet"
});
//Made union with not publised result with not published result
var studentResult = studentPublishedResult.Union(studentCourseWithoutResult).ToList();
var resultCourses = new List <ResultCourseView>();
//with foreach loop adding the course grade to result course view models results view
foreach (var result in studentResult)
{
resultCourses.Add(new ResultCourseView()
{
CourseCode = result.CourseCode,
CourseName = result.CourseName,
Grade = result.Grade
});
}
//populating the object of student result view model for the pdf
var studentResultView = new StudentResultViewModel()
{
StudentDepartment = aStudent.Department.DepartmentName,
StudentName = aStudent.StudentName,
StudentEmail = aStudent.Email,
StudentRegistrationNumber = aStudent.RegistrationNumber,
ResultCourseViews = resultCourses
};
return(studentResultView);
}
