using System; using System.Collections.Generic; using System.Linq; using System.Text; using EMIS.ViewModel.DQPSystem; using System.Linq.Expressions; using EMIS.Entities; using EMIS.DataLogic.Repositories; namespace EMIS.DataLogic.DQPSystem { public class SOCScoreDAL { public SOCDetailStudentScoreRepository SOCDetailStudentScoreRepository { get; set; } public SOCDetailRepository SOCDetailRepository { get; set; } public SOCRepository SOCRepository { get; set; } public SchoolyearRepository SchoolyearRepository { get; set; } public CoursematerialRepository CoursematerialRepository { get; set; } public CollegeRepository CollegeRepository { get; set; } public FacultymajorRepository FacultymajorRepository { get; set; } public GrademajorRepository GrademajorRepository { get; set; } public ClassmajorRepository ClassmajorRepository { get; set; } public UserRepository UserRepository { get; set; } public IQueryable GetSOCCourseScoreViewQueryable(Expression> socExp, Expression> detailExp, Expression> facultyExp, Expression> gradeExp, Expression> classExp) { var sql = (from soc in SOCRepository.GetList(socExp) 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 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 join college in CollegeRepository.Entities on faculty.CollegeID equals college.CollegeID from score in SOCDetailStudentScoreRepository.Entities .Where(x => x.SOCDetailID == detail.SOCDetailID && x.UserID == student.UserID).DefaultIfEmpty() group new { Detail = detail, Score = score } by new { soc.SOCID, soc.SchoolyearID, SchoolyearCode = schoolyear.Code, course.CoursematerialID, course.CourseCode, course.CourseName, TotalCredit = soc.Credit, college.CollegeID, CollegeNo = college.No, CollegeName = college.Name, student.ClassmajorID, ClassmajorNo = classmajor.No, ClassmajorName = classmajor.Name, Gradeyear = grade.GradeID, faculty.StandardID, student.UserID, user.LoginID, user.Name } into g select new SOCCourseScoreView { SOCID = g.Key.SOCID, SchoolyearID = g.Key.SchoolyearID, SchoolyearCode = g.Key.SchoolyearCode, CoursematerialID = g.Key.CoursematerialID, CourseCode = g.Key.CourseCode, CourseName = g.Key.CourseName, TotalCredit = g.Key.TotalCredit, CollegeID = g.Key.CollegeID, CollegeNo = g.Key.CollegeNo, Gradeyear = g.Key.Gradeyear, StandardID = g.Key.StandardID, CollegeName = g.Key.CollegeName, ClassmajorID = g.Key.ClassmajorID, ClassmajorNo = g.Key.ClassmajorNo, ClassmajorName = g.Key.ClassmajorName, UserID = g.Key.UserID, LoginID = g.Key.LoginID, Name = g.Key.Name, TotalScore = g.Sum(x => x.Score.Score) == null ? (decimal?)null : g.Sum(x => (x.Score.Score ?? 0) * (x.Detail.Weight ?? 0) / 100), Credit = g.Sum(x => x.Score.Score) == null ? (decimal?)null : (g.Sum(x => (x.Score.Score ?? 0) * (x.Detail.Weight ?? 0) / 100) > 60 ? g.Key.TotalCredit : 0) }); return sql; } } }