JW/EMiS.DataLogic/DQPSystem/SOCScoreDAL.cs

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<SOCCourseScoreView> GetSOCCourseScoreViewQueryable(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)
        {
            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;
        }
    }
}