using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EMIS.DataLogic.Repositories;
using EMIS.ViewModel.DQPSystem;
using EMIS.Entities;
using System.Linq.Expressions;
using EMIS.ViewModel;
using EMIS.ViewModel.Students;
using EMIS.DataLogic.Common.Students;

namespace EMIS.DataLogic.DQPSystem
{
    public class SOCDetailScoreDAL
    {
        public SOCDetailStudentScoreRepository SOCDetailStudentScoreRepository { get; set; }
        public SOCDetailStudentAttachmentRepository SOCDetailStudentAttachmentRepository { get; set; }
        public SOCDetailGroupRepository SOCDetailGroupRepository { get; set; }
        public SOCDetailStudentRepository SOCDetailStudentRepository { get; set; }
        public SOCDetailRepository SOCDetailRepository { get; set; }
        public SOCRepository SOCRepository { get; set; }
        public SchoolyearRepository SchoolyearRepository { get; set; }
        public CoursematerialRepository CoursematerialRepository { get; set; }
        public FacultymajorRepository FacultymajorRepository { get; set; }
        public GrademajorRepository GrademajorRepository { get; set; }
        public ClassmajorRepository ClassmajorRepository { get; set; }
        public CollegeRepository CollegeRepository { get; set; }
        public StudentRepository StudentRepository { get; set; }
        public UserRepository UserRepository { get; set; }
        public DepartmentRepository DepartmentRepository { get; set; }
        public EducationMissionRepository educationMissionRepository { get; set; }
        public Lazy<StudentsDAL> StudentsDAL { get; set; }

        public IQueryable<SOCDetailStudentScoreView> GetSOCDetailStudentScoreView(Guid userID, Expression<Func<DQP_SOC, bool>> socExp, 
            Expression<Func<CF_Facultymajor, bool>> facultyExp, 
            Expression<Func<CF_Grademajor, bool>> gradeExp,
            Expression<Func<CF_Classmajor, bool>> classExp)
        {
            var sql = (from soc in SOCRepository.GetList(socExp).Where(x => x.DQP_SOCStaff.Any(w => w.UserID == userID))
                       join edu in educationMissionRepository.Entities
                       on soc.EducationMissionID equals edu.EducationMissionID
                       into gedu
                       from edu in gedu.DefaultIfEmpty()
                       join schoolyear in SchoolyearRepository.Entities on soc.SchoolyearID equals schoolyear.SchoolyearID 
                       join course in CoursematerialRepository.Entities on soc.CoursematerialID equals course.CoursematerialID 
                       join detail in SOCDetailRepository.Entities.Where(x => x.IsGroup != true && x.CreateUserID == userID) on soc.SOCID equals detail.SOCID 
                       from student in soc.CF_Student 
                       join user in UserRepository.Entities on student.UserID equals user.UserID 
                       from socTeacher in student.DQP_SOCStaff.Where(x => x.SOCID == soc.SOCID && x.UserID == userID && x.TeachingMethod == (int)EM_TeachingMethod.Lecturer)
                       //join classmajor in ClassmajorRepository.Entities.Where(classExp) on student.ClassmajorID equals classmajor.ClassmajorID 
                       //join grade in GrademajorRepository.Entities.Where(gradeExp) on classmajor.GrademajorID equals grade.GrademajorID
                       //join faculty in FacultymajorRepository.Entities.Where(facultyExp) on grade.FacultymajorID equals faculty.FacultymajorID 
                       from studentdetail in SOCDetailStudentRepository.Entities.Where(x => x.SOCDetailID == detail.SOCDetailID 
                            && x.UserID == student.UserID).DefaultIfEmpty() 
                       from score in SOCDetailStudentScoreRepository.Entities
                            .Where(x => x.SOCDetailID == detail.SOCDetailID && x.UserID == student.UserID).DefaultIfEmpty() 
                       select new SOCDetailStudentScoreView
                       {
                           SOCID = soc.SOCID,
                           EducationMissionID = soc.EducationMissionID,
                           EducationMissionName = edu.ClassName,
                           SchoolyearID = soc.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CoursematerialID = course.CoursematerialID,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           ClassmajorID = student.ClassmajorID,
                           //ClassmajorNo = classmajor.No,
                           //ClassmajorName = classmajor.Name,
                           UserID = student.UserID,
                           LoginID = user.LoginID,
                           Name = user.Name,
                           SOCDetailStudentID = studentdetail.SOCDetailStudentID,
                           SOCDetailID = detail.SOCDetailID,
                           SOCDetailName = detail.Name,
                           SOCDetailCredit = detail.Credit,
                           Weight = detail.Weight,
                           Score = score.Score,
                           Credit = score.Credit,
                           Remark = studentdetail.Remark,
                           RecordStatus = studentdetail.RecordStatus ?? (int)DQP_SOCDetailSubmitStatus.NotSubmit
                       });

            return sql;
        }
        public IQueryable<SOCDetailStudentScoreView> GetSOCDetailView(Guid userID, Expression<Func<DQP_SOC, bool>> socExp,
            Expression<Func<CF_Facultymajor, bool>> facultyExp,
            Expression<Func<CF_Grademajor, bool>> gradeExp,
            Expression<Func<CF_Classmajor, bool>> classExp)
        {
            var sql = (from soc in SOCRepository.GetList(socExp).Where(x => x.DQP_SOCStaff.Any(w => w.UserID == userID))
                       join edu in educationMissionRepository.Entities
                       on soc.EducationMissionID equals edu.EducationMissionID
                       into gedu
                       from edu in gedu.DefaultIfEmpty()
                       join schoolyear in SchoolyearRepository.Entities on soc.SchoolyearID equals schoolyear.SchoolyearID
                       join course in CoursematerialRepository.Entities on soc.CoursematerialID equals course.CoursematerialID
                       join detail in SOCDetailRepository.Entities.Where(x => x.IsGroup != true) on soc.SOCID equals detail.SOCID
                       from student in soc.CF_Student
                       join user in UserRepository.Entities on student.UserID equals user.UserID
                       join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                       join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                       from studentdetail in SOCDetailStudentRepository.Entities.Where(x => x.SOCDetailID == detail.SOCDetailID
                            && x.UserID == student.UserID).DefaultIfEmpty()
                       from score in SOCDetailStudentScoreRepository.Entities
                            .Where(x => x.SOCDetailID == detail.SOCDetailID && x.UserID == student.UserID).DefaultIfEmpty()
                       from attachment in studentdetail.DQP_SOCDetailStudentAttachment 
                       select new SOCDetailStudentScoreView
                       {
                           SOCID = soc.SOCID,
                           EducationMissionID = soc.EducationMissionID,
                           EducationMissionName = edu.ClassName,
                           SchoolyearID = soc.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CoursematerialID = course.CoursematerialID,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           ClassmajorID = student.ClassmajorID,
                           YearID = grade.GradeID,
                           UserID = student.UserID,
                           LoginID = user.LoginID,
                           Name = user.Name,
                           SOCDetailStudentID = studentdetail.SOCDetailStudentID,
                           SOCDetailID = detail.SOCDetailID,
                           SOCDetailName = detail.Name,
                           SOCDetailCredit = detail.Credit,
                           Weight = detail.Weight,
                           Score = score.Score,
                           Credit = score.Credit,
                           Remark = studentdetail.Remark,
                           RecordStatus = studentdetail.RecordStatus ?? (int)DQP_SOCDetailSubmitStatus.NotSubmit,
                           SOCDetailUrl=attachment.Url,
                           FileName = attachment.Name
                       });

            return sql;
        }
        public IQueryable<SOCDetailStudentScoreView> GetSOCDetailView(Expression<Func<DQP_SOCDetail, bool>> socExp,Guid? userID)
        {
            var sql = (from soc in SOCRepository.GetList(x => true)
                       join edu in educationMissionRepository.Entities
                       on soc.EducationMissionID equals edu.EducationMissionID
                       into gedu
                       from edu in gedu.DefaultIfEmpty()
                       join schoolyear in SchoolyearRepository.Entities on soc.SchoolyearID equals schoolyear.SchoolyearID
                       join course in CoursematerialRepository.Entities on soc.CoursematerialID equals course.CoursematerialID
                       join detail in SOCDetailRepository.GetList(socExp).Where(x => x.IsGroup != true) on soc.SOCID equals detail.SOCID
                       from student in soc.CF_Student
                       join user in UserRepository.Entities.Where(x => x.UserID == userID) on student.UserID equals user.UserID
                       join classmajor in ClassmajorRepository.Entities on student.ClassmajorID equals classmajor.ClassmajorID
                       join grade in GrademajorRepository.Entities on classmajor.GrademajorID equals grade.GrademajorID
                       from studentdetail in SOCDetailStudentRepository.Entities.Where(x => x.SOCDetailID == detail.SOCDetailID
                            && x.UserID == student.UserID).DefaultIfEmpty()
                       from score in SOCDetailStudentScoreRepository.Entities
                            .Where(x => x.SOCDetailID == detail.SOCDetailID && x.UserID == student.UserID).DefaultIfEmpty()
                       select new SOCDetailStudentScoreView
                       {
                           SOCID = soc.SOCID,
                           EducationMissionID = soc.EducationMissionID,
                           EducationMissionName = edu.ClassName,
                           SchoolyearID = soc.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CoursematerialID = course.CoursematerialID,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           ClassmajorID = student.ClassmajorID,
                           YearID = grade.GradeID,
                           UserID = student.UserID,
                           LoginID = user.LoginID,
                           Name = user.Name,
                           SOCDetailStudentID = studentdetail.SOCDetailStudentID,
                           SOCDetailID = detail.SOCDetailID,
                           SOCDetailName = detail.Name,
                           SOCDetailCredit = detail.Credit,
                           Weight = detail.Weight,
                           Score = score.Score,
                           Credit = score.Credit,
                           Remark = studentdetail.Remark,
                           RecordStatus = studentdetail.RecordStatus ?? (int)DQP_SOCDetailSubmitStatus.NotSubmit
                       });

            return sql;
        }
        public IQueryable<SOCDetailRawScoreView> GetSOCDetailRawScoreView(Expression<Func<DQP_SOC, bool>> socExp,
            Expression<Func<DQP_SOCDetail, bool>> detailExp,
            Expression<Func<CF_Facultymajor, bool>> facultyExp,
            Expression<Func<CF_Grademajor, bool>> gradeExp,
            Expression<Func<CF_Classmajor, bool>> classExp,
            Expression<Func<Sys_User, bool>> userExp)
        {
            var sql = (from soc in SOCRepository.GetList(socExp)
                       join department in DepartmentRepository.Entities on soc.DepartmentID equals department.DepartmentID
                       into fdep
                       from department in fdep.DefaultIfEmpty()
                       join schoolyear in SchoolyearRepository.Entities on soc.SchoolyearID equals schoolyear.SchoolyearID
                       join course in CoursematerialRepository.Entities on soc.CoursematerialID equals course.CoursematerialID
                       join detail in SOCDetailRepository.GetList(detailExp) on soc.SOCID equals detail.SOCID
                       from student in soc.CF_Student
                       join user in UserRepository.Entities.Where(userExp) on student.UserID equals user.UserID
                       join classmajor in ClassmajorRepository.Entities.Where(classExp) on student.ClassmajorID equals classmajor.ClassmajorID
                       join grade in GrademajorRepository.Entities.Where(gradeExp) on classmajor.GrademajorID equals grade.GrademajorID
                       join faculty in FacultymajorRepository.Entities.Where(facultyExp) on grade.FacultymajorID equals faculty.FacultymajorID
                       from score in SOCDetailStudentScoreRepository.Entities
                            .Where(x => x.SOCDetailID == detail.SOCDetailID && x.UserID == student.UserID).DefaultIfEmpty() 
                       from creator in UserRepository.Entities.Where(x => x.UserID == score.CreateUserID).DefaultIfEmpty() 
                       select new SOCDetailRawScoreView
                       {
                           SOCID = soc.SOCID,
                           SchoolyearID = soc.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CoursematerialID = course.CoursematerialID,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           DepartmentID = soc.DepartmentID,
                           CollegeID = department.CollegeID,
                           TotalCredit = soc.Credit,
                           ClassmajorID = student.ClassmajorID,
                           ClassmajorNo = classmajor.No,
                           ClassmajorName = classmajor.Name,
                           Gradeyear = grade.GradeID,
                           StandardID = faculty.StandardID,
                           UserID = student.UserID,
                           LoginID = user.LoginID,
                           Name = user.Name,
                           SOCDetailID = detail.SOCDetailID,
                           SOCDetailName = detail.Name,
                           SOCDetailCredit = detail.Credit,
                           Weight = detail.Weight,
                           Score = score.Score,
                           Credit = score.Credit,
                           CreateTime = score.CreateTime,
                           CreateUserID = score.CreateUserID,
                           CreateUserName = creator.Name
                       });

            return sql;
        }

        public IOrderedQueryable<FileUploadView> GetSOCDetailStudentAttachmentView(Expression<Func<DQP_SOCDetailStudent, bool>> exp)
        {
            var tableName = typeof(DQP_SOCDetailStudentAttachment).Name;
            var sql = (from student in SOCDetailStudentRepository.GetList(exp)
                       from attachment in student.DQP_SOCDetailStudentAttachment 
                       orderby attachment.CreateTime descending 
                       select new FileUploadView 
                       {
                           FileID = attachment.SOCDetailStudentAttachmentID,
                           FileName = attachment.Name,
                           FileUrl = attachment.Url,
                           FormID = attachment.SOCDetailStudentID,
                           TableName = tableName
                       });

            return (IOrderedQueryable<FileUploadView>)sql;
        }

        public IOrderedQueryable<FileUploadView> GetSOCDetailGroupAttachmentView(Expression<Func<DQP_SOCDetailGroup, bool>> exp)
        {
            var tableName = typeof(DQP_SOCDetailStudentAttachment).Name;
            var sql = (from @group in SOCDetailGroupRepository.GetList(exp)
                       from attachment in @group.DQP_SOCDetailStudentAttachment
                       orderby attachment.CreateTime descending
                       select new FileUploadView
                       {
                           FileID = attachment.SOCDetailStudentAttachmentID,
                           FileName = attachment.Name,
                           FileUrl = attachment.Url,
                           FormID = attachment.SOCDetailGroupID,
                           TableName = tableName
                       });

            return (IOrderedQueryable<FileUploadView>)sql;
        }

        public IQueryable<BaseStudentView> GetSOCDetailGroupStudentView(Expression<Func<DQP_SOCDetailGroup, bool>> exp)
        {
            var baseStudentQuerable = StudentsDAL.Value.GetBaseStudentViewQueryable(x => true);
            var sql = (from @group in SOCDetailGroupRepository.GetList(exp)
                       from student in @group.CF_Student
                       from view in baseStudentQuerable.Where(x => x.UserID == student.UserID)
                       select view);

            return sql;
        }

        public IQueryable<SOCDetailGroupScoreView> GetSOCDetailGroupScoreView(Guid userID, Expression<Func<DQP_SOC, bool>> socExp,
            Expression<Func<CF_Facultymajor, bool>> facultyExp,
            Expression<Func<CF_Grademajor, bool>> gradeExp,
            Expression<Func<CF_Classmajor, bool>> classExp)
        {
            var sql = (from soc in SOCRepository.GetList(socExp).Where(x => x.DQP_SOCStaff.Any(w => w.UserID == userID))
                       join edu in educationMissionRepository.Entities
                       on soc.EducationMissionID equals edu.EducationMissionID
                       into gedu
                       from edu in gedu.DefaultIfEmpty()
                       join schoolyear in SchoolyearRepository.Entities on soc.SchoolyearID equals schoolyear.SchoolyearID
                       join course in CoursematerialRepository.Entities on soc.CoursematerialID equals course.CoursematerialID
                       join detail in SOCDetailRepository.Entities.Where(x => x.IsGroup == true && x.CreateUserID == userID) on soc.SOCID equals detail.SOCID
                       //from classmajor in soc.CF_Classmajor
                       //join grade in GrademajorRepository.Entities.Where(gradeExp) on classmajor.GrademajorID equals grade.GrademajorID
                       //join faculty in FacultymajorRepository.Entities.Where(facultyExp) on grade.FacultymajorID equals faculty.FacultymajorID
                       from @group in SOCDetailGroupRepository.Entities
                            .Where(x => x.SOCDetailID == detail.SOCDetailID && x.CF_Student.Any(w => w.DQP_SOCStaff.Any(v => v.SOCID == soc.SOCID && v.UserID == userID && v.TeachingMethod == (int)EM_TeachingMethod.Lecturer)))
                       select new SOCDetailGroupScoreView
                       {
                           SOCID = soc.SOCID,
                           EducationMissionID = soc.EducationMissionID,
                           EducationMissionName = edu.ClassName,
                           SchoolyearID = soc.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CoursematerialID = course.CoursematerialID,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           //ClassmajorID = classmajor.ClassmajorID,
                           //ClassmajorNo = classmajor.No,
                           //ClassmajorName = classmajor.Name,
                           SOCDetailGroupID = @group.SOCDetailGroupID,
                           No = @group.No,
                           SOCDetailID = detail.SOCDetailID,
                           SOCDetailName = detail.Name,
                           SOCDetailCredit = detail.Credit,
                           Weight = detail.Weight,
                           Score = @group.Score,
                           Remark = @group.Remark,
                           RecordStatus = @group.RecordStatus
                       });

            return sql;
        }
        public IQueryable<SOCDetailGroupScoreView> GetSOCDetailGroup(Guid userID, Expression<Func<DQP_SOC, bool>> socExp,
            Expression<Func<CF_Facultymajor, bool>> facultyExp,
            Expression<Func<CF_Grademajor, bool>> gradeExp,
            Expression<Func<CF_Classmajor, bool>> classExp)
        {
            var sql = (from soc in SOCRepository.GetList(socExp).Where(x => x.DQP_SOCStaff.Any(w => w.UserID == userID))
                       join edu in educationMissionRepository.Entities
                       on soc.EducationMissionID equals edu.EducationMissionID
                       into gedu
                       from edu in gedu.DefaultIfEmpty()
                       join schoolyear in SchoolyearRepository.Entities on soc.SchoolyearID equals schoolyear.SchoolyearID
                       join course in CoursematerialRepository.Entities on soc.CoursematerialID equals course.CoursematerialID
                       join detail in SOCDetailRepository.Entities.Where(x => x.IsGroup == true && x.CreateUserID == userID) on soc.SOCID equals detail.SOCID
                       from @group in SOCDetailGroupRepository.Entities
                            .Where(x => x.SOCDetailID == detail.SOCDetailID && x.CF_Student.Any(w => w.DQP_SOCStaff.Any(v => v.SOCID == soc.SOCID && v.UserID == userID && v.TeachingMethod == (int)EM_TeachingMethod.Lecturer)))
                       from student in @group.CF_Student
                       from attachment in @group.DQP_SOCDetailStudentAttachment
                       select new SOCDetailGroupScoreView
                       {
                           SOCID = soc.SOCID,
                           EducationMissionID = soc.EducationMissionID,
                           EducationMissionName = edu.ClassName,
                           SchoolyearID = soc.SchoolyearID,
                           SchoolyearCode = schoolyear.Code,
                           CoursematerialID = course.CoursematerialID,
                           CourseCode = course.CourseCode,
                           CourseName = course.CourseName,
                           SOCDetailGroupID = @group.SOCDetailGroupID,
                           No = @group.No,
                           SOCDetailID = detail.SOCDetailID,
                           SOCDetailName = detail.Name,
                           SOCDetailCredit = detail.Credit,
                           Weight = detail.Weight,
                           Score = @group.Score,
                           Remark = @group.Remark,
                           RecordStatus = @group.RecordStatus,
                           SOCDetailUrl=attachment.Url,
                           FileName=attachment.Name
                       });

            return sql;
        }
        public IList<SOCStudentScoreItemView> GetSOCStudentScoreItemViewQueryable(Guid userID)
        {
            var query = from score in SOCDetailStudentScoreRepository.Entities 
                        from detail in SOCDetailRepository.Entities.Where(x => x.SOCDetailID == score.SOCDetailID) 
                        from soc in SOCRepository.Entities.Where(x => x.SOCID == detail.SOCID) 
                        from schoolyear in SchoolyearRepository.Entities.Where(x => x.SchoolyearID == soc.SchoolyearID) 
                        from course in CoursematerialRepository.Entities.Where(x => x.CoursematerialID == soc.CoursematerialID)
                        from student in StudentRepository.Entities.Where(x => score.UserID == x.UserID) 
                        from classmajor in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID) 
                        from grade in GrademajorRepository.Entities.Where(x => x.GrademajorID == classmajor.GrademajorID)
                        from gradeSchoolyear in SchoolyearRepository.Entities.Where(x => x.Years == grade.GradeID && x.SchoolcodeID == grade.SemesterID) 
                        where (score.UserID == userID)
                        group new { Score = score, Detail = detail } by new 
                        {
                            UserID = score.UserID,
                            CourseName = course.CourseName,
                            Credit = soc.Credit,
                            CoursematerialID = course.CoursematerialID,
                            SchoolyearNumID = (schoolyear.Value - gradeSchoolyear.Value - (schoolyear.SchoolcodeID == gradeSchoolyear.SchoolcodeID ? 0 : 1)) / 2 + 1,
                            StarttermID = schoolyear.Value - gradeSchoolyear.Value + 1
                        } into g 
                        orderby new { StarttermID = g.Key.StarttermID, g.Key.CourseName }
                        select new SOCStudentScoreItemView()
                        {
                            UserID = g.Key.UserID,
                            CourseName = g.Key.CourseName,
                            TotalScore = g.Sum(x => (x.Score.Score * x.Detail.Weight) / 100),
                            ScoreCredit = g.Sum(x => (x.Score.Score * x.Detail.Weight) / 100) > 60 ? g.Key.Credit : 0,
                            CoursematerialID = g.Key.CoursematerialID,
                            SchoolyearNumID = g.Key.SchoolyearNumID,
                            StarttermID = g.Key.StarttermID
                        };

            return query.ToList();
        }

        public SOCStudentScoreTotalView GetSOCStudentScoreItemView(Guid userID)
        {
            var query = from student in StudentRepository.Entities.Where(x => x.UserID == userID) 
                        from classmajor in ClassmajorRepository.Entities.Where(x => x.ClassmajorID == student.ClassmajorID) 
                        from grade in GrademajorRepository.Entities.Where(x => x.GrademajorID == classmajor.GrademajorID) 
                        from faculty in FacultymajorRepository.Entities.Where(x => x.FacultymajorID == grade.FacultymajorID) 
                        from college in CollegeRepository.Entities.Where(x => x.CollegeID == faculty.CollegeID) 
                        select new SOCStudentScoreTotalView
                        {
                            UserID = student.UserID,
                            UserName = student.Sys_User.Name,
                            CollegeName = college.Name,
                            ClassName = classmajor.Name,
                            PlanningGraduateDate = student.PlanningGraduateDate
                        };
            return query.FirstOrDefault();
        }

        public IQueryable<DQP_SOCDetailStudentScore> GetStudentScoreBySOCDetailStudent(Expression<Func<DQP_SOCDetailStudent, bool>> exp)
        {
            var query = from student in SOCDetailStudentRepository.GetList(exp)
                        from score in SOCDetailStudentScoreRepository.Entities.Where(x => student.SOCDetailID == x.SOCDetailID && student.UserID == x.UserID)
                        select score;

            return query;
        }

        public IQueryable<DQP_SOCDetailStudentScore> GetStudentScoreBySOCDetailGroup(Expression<Func<DQP_SOCDetailGroup, bool>> exp)
        {
            var query = from @group in SOCDetailGroupRepository.GetList(exp) 
                        from student in @group.CF_Student 
                        from score in SOCDetailStudentScoreRepository.Entities.Where(x => @group.SOCDetailID == x.SOCDetailID && student.UserID == x.UserID)
                        select score;

            return query;
        }
    }
}