public async Task <List <StudentModuleMapping> > GetStudentModuleMappings()
{
string query = @"SELECT SMM.*,S.Name AS StudentName,M.Name AS ModuleName FROM StudentModuleMapping SMM INNER JOIN Student S
ON SMM.StudentId = S.Id INNER JOIN Module M ON SMM.ModuleId= M.Id";
return(await _dao.FetchListAsync <StudentModuleMapping>(query));
}
public async Task <List <Module> > GetModules()
{
string query = @"SELECT M.*,C.Name AS CourseName,S.Name AS SemesterName FROM Module M INNER JOIN COURSE C ON M.CourseId= C.Id
INNER JOIN Semester S ON M.SemesterId = S.Id";
return(await _dao.FetchListAsync <Module>(query));
}
public async Task <List <Timetable> > GetTimetables()
{
string query = @"SELECT T.*,S.Name AS SemesterName, M.Name AS ModuleName,TE.Name AS TeacherName , C.Name as CourseName FROM Timetable T INNER JOIN Semester S ON T.SemesterId = S.Id
INNER JOIN Module M on T.ModuleId = M.Id INNER JOIN Teacher TE ON T.TeacherId = TE.Id INNER JOIN COURSE C ON T.CourseId= C.ID";
return(await _dao.FetchListAsync <Timetable>(query));
}
public async Task <List <StudentReport> > StudentReport(StudentReport param)
{
string qry = @"select s.id as studentId, s.name as StudentName , s.RollNumber, s.AcademicYear, s.EnrollDate
, m.id as moduleId, m.Name as moduleName
, c.Id as courseId, c.Name as courseName
, sem.id as semesterId, sem.Name as semesterName
from student s
inner join StudentModuleMapping SMM ON SMM.StudentId = s.id
inner join module m on m.id = smm.id
inner join course c on c.Id = m.CourseId
inner join semester sem on sem.id = m.SemesterId
where m.courseId = case when @courseId <> -1 then @courseid else m.courseid end
order by EnrollDate asc" ;
List <SqlParam> collection = new List <SqlParam> {
new SqlParam("@courseId", SqlDbType.VarChar, param.CourseId)
};
return(await _dao.FetchListAsync <StudentReport>(qry, collection));
}
public async Task <List <User> > GetUsers()
{
string query = @"SELECT U.*,R.Name AS RoleName FROM [USER] U INNER JOIN [ROLE] R ON U.RoleId = R.Id";
return(await _dao.FetchListAsync <User>(query));
}
public async Task <List <Teacher> > GetTeachers()
{
string query = @"SELECT * FROM TEACHER";
return(await _dao.FetchListAsync <Teacher>(query));
}
public async Task <List <Role> > GetRoles()
{
string query = @"SELECT * FROM ROLE";
return(await _dao.FetchListAsync <Role>(query));
}
public async Task <List <Student> > GetStudents()
{
string query = @"SELECT * FROM STUDENT";
return(await _dao.FetchListAsync <Student>(query));
}
public async Task <List <Course> > GetCourses()
{
string query = @"SELECT * FROM COURSE";
return(await _dao.FetchListAsync <Course>(query));
}